Altering Tables

You can use the ALTER TABLE command to change part of a table definition. You can ADD, MODIFY, or DROP a column on a live table, although, of course, you should take care to ensure that doing so will not cause errors in an application or query that relies on the existing table structure.

To add a new column, use the ADD keyword followed by a column definition. For example, the following statement adds a new column named fax to the customer_contacts table, which has a maximum length of 12 characters:

MariaDB> ALTER TABLE customer_contacts
 -> ADD fax VARCHAR(12);
Query OK, 11 rows affected (0.00 sec)
Records: 11 Duplicates: 0 Warnings: 0

To modify an existing column, use the MODIFY keyword followed by the existing column name, a new column name, and a column definition. You can use MODIFY both to rename a column and to change its definition.

Suppose you realize that the fax column is not long enough to store international fax numbers, so you want to make this an unlimited-length TEXT type column. The following statement changes the data type of fax. Notice that the column name appears twice in this statement because you do not want to change its name.

MariaDB> ALTER TABLE customer_contacts
 -> CHANGE fax fax TEXT;
Query OK, 11 rows affected (0.01 sec)
Records: 11 Duplicates: 0 Warnings: 0

To drop a column from a table, use the DROP keyword followed by the column name. The following statement drops the fax column from the customer_contacts table:

MariaDB> ALTER TABLE customer_contacts
 -> DROP fax;
Query OK, 11 rows affected (0.00 sec)
Records: 11 Duplicates: 0 Warnings: 0