How to Use the ALTER TABLE Statement
In this tutorial, you'll learn how to use the MySQL ALTER TABLE statement to add, drop, and modify columns in an existing table. You also can use the ALTER TABLE statement to add and drop a primary key, a foreign key, and other constraints on the table.
Adding a single column using ADD COLUMN
You can add a single column to a given existing table using the following syntax.
ALTER TABLE table_name
ADD [COLUMN] column_name column_definition [FIRST | AFTER column_name];
Let's say you have a table named customers, which consists of 2 columns, named cutomer_id and cusotmer_name in your database.
Now let's add a new column named joined_date after the customer_name column to the customers table.
The SQL statement will look like this:
ALTER TABLE customers
ADD joined_date DATE NULL AFTER customer_name;
Adding multiple columns using the ADD COLUMN statement
You can also add more than columns at a time.
To add more than columns, use the following syntax:
ALTER TABLE table_name
ADD [COLUMN] column_name column_definition [FIRST | AFTER column_name],
ADD [COLUMN] column_name column_definition [FIRST | AFTER column_name],
...ADD [COLUMN] column_name column_definition [FIRST | AFTER column_name];
Here is an example of how to add two columns:
ALTER TABLE customers
ADD COLUMN joined_date DATE NULL AFTER customer_name,
ADD address VARCHAR(200) NULL FIRST;
MySQL adds two new columns named joined_date and address to the customers table. MySQL adds the joined_date column after the customer_name column and address column to the first position of the table.
Dropping a column using the DROP COLUMN statement
You can use the ALTER TABLE statement with the DROP COLUMN statement to drop a column from a given table.
To drop a column, use the following syntax:
ALTER TABLE table_name
DROP COLUMN column_name;
Let's say you need to drop two columns named customer_name and joined_date from the customers table. You can write the SQL statement as follows:
ALTER TABLE customers
DROP COLUMN customer_name;
DROP COLUMN joined_date;
Modifying a Column Using ALTER COLUMN
To modify a column definition in a given table, use the following syntax:
ALTER TABLE table_name
ALTER COLUMN column_name column_definition [FIRST | AFTER column_name];
Let's say you have a table named suppliers, which consists of three columns, supplier_name, supplier_code, and group in the order in your database.
Now let's change the definitions of the supplier_name and group columns. The supplier_name column can hold up to 200 characters, and we place it after the supplier_code column.
And the group column can hold up to 40 characters, and we place it after the supplier_name column.
The SQL statement will like this:
ALTER TABLE suppliers
ALTER COLUMN supplier_name VARCHAR(200) AFTER supplier_code,
ALTER COLUMN group VARCHAR(40) AFTER supplier_name;
Renaming a column using RENAME COLUMN
To rename a column without modifying its definition, you can use the following syntax:
ALTER TABLE table_name
RENAME COLUMN old_column_name TO new_column_name;
Here is an example:
ALTER TABLE customers
RENAME COLUMN customer_name TO customer_name2;
The above statement renames the customer_name column to customer_name2.
Renaming a column using CHANGE COLUMN
To rename and change a column definition, use the following syntax:
ALTER TABLE table_name
CHANGE [COLUMN] old_column_name new_column_name column_definition [FIRST | AFTER column_name];
Here is an example:
ALTER TABLE customers
CHANGE customer_name joined_date DATE NULL FIRST;
The above statement renames the name of the customer_name column to joined_date, defines its data type as DATE that can contain a NULL value, and place it in the position of the customers table.
Renaming a table using the RENAME statement
To rename a table, you use the following syntax:
ALTER TABLE table_name
RENAME [TO | AS] new_table_name;
The following illustrates an example of renaming the existing table named suppliers to vendors:
ALTER TABLE suppliers
RENAME vendors;
Note that you also can rename a table using the RENAME TABLE statement.
In this tutorial, you've learned how to use the MySQL ALTER TABLE statement to add, modify, rename, and drop a column in an existing table. You can also use the ALTER TABLE statement to add or drop a primary key, foreign key, or other constraints in the table.