SQL ALTER TABLE Statement
In this tutorial you will learn how to alter or modify an existing table using SQL.
Modifying Existing Tables
It is quite possible that after creating a table, as you start using it, you may discover you’ve forgot to mention any column or constraint or specified a wrong name for the column.
In such situation you can use the ALTER TABLE statement to alter or change an existing table by adding, changing, or deleting a column in the table.
Consider we’ve a shippers table in our database, whose structure is as follows:
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+----------------+
| shipper_id | int | NO | PRI | NULL | auto_increment |
| shipper_name | varchar(60) | NO | | NULL | |
| phone | varchar(60) | NO | | NULL | |
+--------------+-------------+------+-----+---------+----------------+
We’ll use this shippers table for all of our ALTER TABLE statements.
Now suppose that we want to expand the existing shippers table by adding one more column. But, the question is how we can do this using SQL commands? Well let’s find out.
Adding a New Column
The basic syntax for adding a new column to an existing table can be given with:
ALTER TABLE table_name ADD column_name data_type constraints;
The following statement adds a new column fax to the shippers table.
Example
ALTER TABLE shippers ADD fax VARCHAR(20);
Now, after executing the above statement if you see the table structure using the command DESCRIBE shippers; on MySQL command-line, it looks as follow:
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+----------------+
| shipper_id | int | NO | PRI | NULL | auto_increment |
| shipper_name | varchar(60) | NO | | NULL | |
| phone | varchar(60) | NO | | NULL | |
| fax | varchar(20) | YES | | NULL | |
+--------------+-------------+------+-----+---------+----------------+
Note: If you want to add a NOT NULL column to an existing table then you must specify an explicit default value. This default value is used to populate the new column for every row that already exists in your table.
Tip: When adding a new column to the table, if neither NULL nor NOT NULL is specified, the column is treated as though NULL had been specified.
MySQL add new columns at the end by default. However, if you want to add a new column after a specific column you can use the AFTER clause, as follow:
mysql> ALTER TABLE shippers ADD fax VARCHAR(20) AFTER shipper_name;
MySQL provide another clause FIRST that you can use to add a new column at first place within a table. Just replace the clause AFTER with FIRST in the previous example to add the column fax at the beginning of the shippers table.
Changing Column Position
In MySQL, if you’ve already created a table but unhappy with the existing column position within the table, you can change it any time using the following syntax:
ALTER TABLE table_name MODIFY column_name column_definition AFTER column_name;
The following statement place the column fax after shipper_name column in shippers table.
mysql> ALTER TABLE shippers MODIFY fax VARCHAR(20) AFTER shipper_name;
Adding Constraints
Our current shippers table has one major problem. If you insert records with duplicate phone numbers it wouldn’t stop you from doing that, which is not good, it should be unique.
You can fix this by adding a constraint UNIQUE to the phone column. The basic syntax for adding this constraint to existing table columns can be given with:
ALTER TABLE table_name ADD UNIQUE (column_name,…);
The following statement adds a constraint UNIQUE to the phone column.
mysql> ALTER TABLE shippers ADD UNIQUE (phone);
After executing this statement if you try to insert a duplicate phone number, you’ll get an error.
Similarly, if you’ve created a table without a PRIMARY KEY, you can add one with:
ALTER TABLE table_name ADD PRIMARY KEY (column_name,…);
The following statement adds a constraint PRIMARY KEY to the shipper_id column, if not defined.
mysql> ALTER TABLE shippers ADD PRIMARY KEY (shipper_id);
Removing Columns
The basic syntax for removing a column from an existing table can be given with:
ALTER TABLE table_name DROP COLUMN column_name;
The following statement removes our newly added column fax from the shippers table.
mysql> ALTER TABLE shippers DROP COLUMN fax;
Now, after executing the above statement if you see the table structure, it looks as follow:
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+----------------+
| shipper_id | int | NO | PRI | NULL | auto_increment |
| shipper_name | varchar(60) | NO | | NULL | |
| phone | varchar(20) | NO | UNI | NULL | |
+--------------+-------------+------+-----+---------+----------------+
Changing Data Type of a Column
You can modify the data type of a column in SQL Server by using the ALTER clause, as follow:
ALTER TABLE table_name ALTER COLUMN column_name new_data_type;
The MySQL database server however does not support the ALTER COLUMN syntax. It supports an alternate MODIFY clause that you can use to modify the column as follows:
ALTER TABLE table_name MODIFY column_name new_data_type;
The following statement changes the current data type of the phone column in our shippers table from VARCHAR to CHAR and length from 20 to 15.
mysql> ALTER TABLE shippers MODIFY phone CHAR(15);
Similarly, you can use the MODIFY clause to switch between whether a column in the MySQL table should allow null values or not by re-specifying the existing column definition and add the NULL or NOT NULL constraint at the end, like this:
mysql> ALTER TABLE shippers MODIFY shipper_name CHAR(15) NOT NULL;
Renaming Tables
The basic syntax for renaming an existing table in MySQL can be given with:
ALTER TABLE current_table_name RENAME new_column_name;
The following statement renames our shippers table shipper.
mysql> ALTER TABLE shippers RENAME shipper;
You can also achieve the same thing in MySQL using the RENAME TABLE statement, as follow:
mysql> RENAME TABLE shippers TO shipper;