SQL ALTER TABLE statement

Adding a new column

Imagine a new international company with employees worldwide, and you're trying to help them keep their records. There is the employees table that looks as follows:

The table doesn't store any contact information for now, but having everyone's contact emails would be great. There is no column for this information, so we must add one.

Add a new column to the table with a simple query using the ALTER TABLE statement with ADD COLUMN.

The following query will add the employee_email column to our employees table:

ALTER TABLE employees 
ADD COLUMN employee_email VARCHAR(10);

Specify the column type in the query as we do when creating a table with new columns. The employee_email column will have the VARCHAR(10) data type.

After the query execution, the table has an empty column for contact emails:

Changing the data type

We created the employee_email column with the VARCHAR(10) data type, but some have long emails like '[email protected]'. This email will not fit the limit, so we won't be able to add it unless we change the column's data type

To change the data type, make a query with ALTER TABLE statement and MODIFY COLUMN:

ALTER TABLE employees 
MODIFY COLUMN employee_email VARCHAR(45);

As a result of the query execution, the employee_email column will have the VARCHAR(45) type. Now, we can add long emails to our table:

To change the column type, it should either be empty before the change, or the new data type must be compatible with the old one. Otherwise, there is going to be an error.

Note that this is a MySQL example and syntax for changing the data type. In other SQL dialects, this query will look a bit different.

Dropping an existing column

In the employees table, there is information about the native city. For the sake of conciseness, let's get rid of this column.

To drop this column from the table, use the following query with the ALTER TABLE statement and DROP COLUMN:

ALTER TABLE employees 
DROP COLUMN native_city;

The employees table will look as follows:

This query deletes the column with all the information stored in it.

Renaming a column

Speaking of conciseness, we can change the employee_email column contains emails belonging to employees. Let's change the column name to email. You can do it with a simple query with the ALTER TABLE statement and CHANGE:

ALTER TABLE employees 
CHANGE employee_email email VARCHAR(45);

Now, our column with emails has a shorter name:

As you can see, there is a type declaration in the query: if you want to change the name and type, you can do it simultaneously. Otherwise, add the previous column type to the query, as in the example above.

We provide MySQL syntax for renaming a column. Remember that not all SQL dialects have the same syntax for changing a column's name, and some might not have it at all.

General templates

To add a new column to the existing table:

ALTER TABLE table_name 
ADD COLUMN column_name DATATYPE;

To delete a column from the table:

ALTER TABLE table_name 
DROP COLUMN column_name;

To change the column type:

ALTER TABLE table_name 
MODIFY COLUMN column_name NEWDATATYPE;

To change the column name (and, possibly, datatype):

ALTER TABLE table_name 
CHANGE old_column_name new_column_name NEWDATATYPE;

In different SQL dialects the syntax may vary.