SQL Modify Table (ALTER TABLE statement)

In this tutorial, you will learn how to use SQL to change or modify an existing table.

Modify an existing table

After creating a table, it is likely that when you start using it, you may find that you have forgotten to create the required columns or constraints, or you have specified an incorrect name for the column.

In this case, you can use the ALTER TABLE statement to change or modify an existing table by adding, changing, or deleting columns in the table.

Consider usshippersThere is a table in the database with the following structure:

+--------------+-------------+------+-----+---------+----------------+
|　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 will use this shippers table for all ALTER table statements.

Now, let's assume we want to expand the existing shippers table by adding a column. But, the question is how do we use SQL commands to do this? Let's take a look.

Adding a new column

　Basic syntax for adding a new column to an existing table:

ALTER TABLE table_name ADD column_name data_type constraints;					

The following statement adds a new column fax to the shippers table.

ALTER TABLE shippers ADD fax VARCHAR(20);

Now, after executing the above statements, if you use the command DESCRIBE shippers to see the table structure; in the MySQL command line, as shown below:

+--------------+-------------+------+-----+---------+----------------+
|　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　　　　|　　　　　　　　　　　　　　　　|
|2| fax | varchar(
+--------------+-------------+------+-----+---------+----------------+

By default, MySQL adds new columns at the end. However, if you want to add a new column after a specific column, you can use the following AFTER clause:

mysql> ALTER TABLE shippers ADD fax VARCHAR(20) AFTER shipper_name;					

MySQL first provided another clause that you can use to add a new column at the first position of the table. Simply replace the AFTER clause in the previous example with FIRST to addshippersAdd the column fax at the beginning of the table.

Change column position

In MySQL, if you have already created a table but are not satisfied with the position of the existing columns in the table, you can use the following syntax to change it at any time:

ALTER TABLE table_name
　　MODIFY column_name column_definition AFTER column_name;			

mysql> ALTER TABLE shippers MODIFY fax VARCHAR(20) AFTER shipper_name;					

Add constraints

Our current shippers table has a major problem. Even if you insert records with duplicate phone numbers, it will not prevent you from doing so, which is not good. Phone numbers should be unique.

You can add constraints byphoneAdd UNIQUE constraints to the existing table columns to solve this problem. The basic syntax for adding constraints to existing table columns is as follows:

ALTER TABLE table_name ADD UNIQUE (column_name,...);					

The following statement UNIQUE is added tophoneAdd column constraints.

mysql> ALTER TABLE shippers ADD UNIQUE (phone);					

After executing this statement, if you try to insert a duplicate phone number, you will receive an error message.

Similarly, if the table you create does not have a PRIMARY KEY, you can use the following content:

ALTER TABLE table_name ADD PRIMARY KEY (column_name,...);					

If not defined, the following statement willPRIMARY KEY constraint is added toshipper_idcolumn.

mysql>　ALTER　TABLE　shippers　ADD　PRIMARY　KEY　(shipper_id);					

Delete column

Basic syntax for deleting a column from an existing table:

ALTER　TABLE　table_name　DROP　COLUMN　column_name;					

The following statement fromshippersfrom the table we just addedfaxcolumn.

mysql>　ALTER　TABLE　shippers　DROP　COLUMN　fax;					

Now, after executing the above statement, if you see the table structure, it will look like this:

+--------------+-------------+------+-----+---------+----------------+
|　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　　　　|　　　　　　　　　　　　　　　　|
+--------------+-------------+------+-----+---------+----------------+

Change column data type

You can use the following ALTER clause to modify column data types in SQL Server:

ALTER　TABLE　table_name　ALTER　COLUMN　column_name　new_data_type;					

However, the MySQL database server does not support the ALTER COLUMN syntax. It supports alternative MODIFY clauses that can be used to modify columns, as shown below:

ALTER　TABLE　table_name　MODIFY　column_name　new_data_type;					

The following statement willshippersin the tablephoneChanging the current data type of the column from VARCHAR to CHAR, the length from20 to15.

mysql>　ALTER　TABLE　shippers　MODIFY　phone　CHAR(15);					

Similarly, you can toggle whether a column in a MySQL table allows null values using the MODIFY clause, by re-specifying the existing column definition and adding a null or not null constraint at the end, as shown below:

mysql>　ALTER　TABLE　shippers　MODIFY　shipper_name　CHAR(15)　NOT　NULL;					

Rename Table

Basic syntax for renaming an existing table in MySQL:

ALTER　TABLE　current_table_name　RENAME　new_column_name;					

The following statement renames ourshippersTable Renamed toshipper.

mysql>　ALTER　TABLE　shippers　RENAME　shipper;					

You can also use the following RENAME TABLE statement to achieve the same purpose in MySQL:

mysql>　RENAME　TABLE　shippers　TO　shipper;