Altering Tables ( Add Columns )

After creating a table, there might be a change of requirements that forces changes to the structure of the table.  If that happens, the table's structure can be modified without having to re-create the table.  This section addresses some of the methods available for modifying the table structure and data. 


Add Columns 


For example, to add a column named Id to the eu_countries table, the following statement can be issued: 


ALTER TABLE eu_countries ADD Id INT(6) NOT NULL; 
That ALTER TABLE/ADD statement changes the table structure as follows: 


mysql> DESCRIBE eu_countries; 


+----------------------+-------------------+------+-----+-----------+--------+ 
| Field                    | Type                | Null | Key | Default  | Extra | 
+----------------------+-------------------+------+-----+-----------+--------+ 
| name                   | char(52)          | NO   |       | NULL    |           | 
| new_population | decimal(12,1) | NO   |       | 0.0        |           | 
| id                         | int(6)                | NO   |       | NULL    |           | 
+----------------------+-------------------+------+-----+-----------+-------+ 
3 rows in set (#.## sec) 


Note: Column names within a table must be unique, so columns with the same name can not be added as one that already exists in the table.  Also, column names are not case sensitive, so if the table already contains a column named id, a new column can not be added using any of these names: ID, id, Id, or iD. 


They all are considered to be the same. 


To add a column at a specific position within a table row, use FIRST or AFTER col_name.  The default is to add the column last. Adding a column to a table does  not populate the rows with standard values (unless a default value or auto_increment is selected). In a query, they will appear as default or NULL (if allowed) values: 


mysql> SELECT * FROM eu_countries; 


+---------------------------------+---------------------+----+ 
| Name                                | NewPopulation | Id  | 
+---------------------------------+---------------------+----+ 
| Albania                              |     5101800.0   |  0   | 
| Andorra                             |      117000.0     |  0  | 
| Austria                               |    12137700.0  |  0  | 
| Belgium                             |    15358500.0  |  0  | 
| Bulgaria                             |    12286350.0  |  0  | 
| Bosnia and Herzegovina |     5958000.0   |  0  | 
:                        :               :    : 
+----------------------------------+--------------------+-----+