SQL Alter Table Statement

The ALTER TABLE statement in SQL is used to add, modify or delete table columns.

We can also use ALTER TABLE statement to add or drop various table constraints.

SQL ALTER TABLE - Add a New Column

To add a new column to an existing table we use the syntax:

ALTER TABLE table_name
ADD column_name datatype;

Example

The following code adds a “Start_Date” column to the “Employees” table:

ALTER TABLE Employees
ADD Start_Date date;

SQL ALTER TABLE - Remove a Column

To remove a column from an existing table we use the syntax:

ALTER TABLE table_name
DROP COLUMN column_name;

Example

The following code removes the “Start_Date” column from the “Employees” table:

ALTER TABLE Employees
DROP COLUMN Start_Date;

SQL ALTER TABLE - Modify a Column

We can use the ALTER TABLE statement to modify a column’s datatype using the syntax:

ALTER TABLE table_name
MODIFY column_name datatype;

Example

The following code changes the datatype of the “Start_Date” column from date to year:

ALTER TABLE Employees
MODIFY Start_Date year;

SQL ALTER TABLE - Demo Database

Suppose we have a table called “Employees” with the following columns:

+------------+-----------+----------+
| EmployeeID | FirstName | LastName |
+------------+-----------+----------+
| 1          | Mark      | Otto     |
| 2          | Jacob     | Thornton |
| 3          | Su        | Bird     |
| 4          | Sam       | Burger   |
+------------+-----------+----------+

Add Column

Now we want to extend the “Employees” table and add a new column called “Start_Date”

ALTER TABLE Employees
ADD Start_Date date;

Output:

+------------+-----------+----------+------------+
| EmployeeID | FirstName | LastName | Start_Date |
+------------+-----------+----------+------------+
| 1          | Mark      | Otto     |            |
| 2          | Jacob     | Thornton |            |
| 3          | Su        | Bird     |            |
| 4          | Sam       | Burger   |            |
+------------+-----------+----------+------------+

Modify Column

Next we want to modify the datatype of the “Start_Date” column from date to year:

ALTER TABLE Employees
MODIFY Start_Date year;

Delete Column

We want to delete the “Start_Date” column from the “Employees” table. We use:

ALTER TABLE Employees
DROP COLUMN Start_Date;

Output:

+------------+-----------+----------+
| EmployeeID | FirstName | LastName |
+------------+-----------+----------+
| 1          | Mark      | Otto     |
| 2          | Jacob     | Thornton |
| 3          | Su        | Bird     |
| 4          | Sam       | Burger   |
+------------+-----------+----------+