How to Change Table Structure in MySQL

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. It is also used to add and drop various constraints on an existing table.

After you create a table, complete with a full set of attributes, you may not want it to remain the same. Requirements have a way of changing, based on changing conditions. The system you are modeling may change, requiring you to change your database structure to match.

1. Adding Column

To add a column in a table without affecting any of the existing columns:

ALTER TABLE table_name ADD COLUMN column_name datatype;

The new column is placed at the last position of the table. However, you can control the positioning of a new column by using an appropriate keyword, including FIRST, AFTER, and LAST. For example,

ALTER TABLE employees ADD COLUMN birthdate DATE AFTER lastname;

2. Deleting Column

To delete a column in a table that is no longer needed:

ALTER TABLE table_name DROP COLUMN column_name;

3. Changing Column Data Type

To change the data type of a column in a table,

ALTER TABLE table_name ALTER COLUMN column_name datatype;