SQL ALTER TABLE

Summary: in this tutorial, you will learn how to use SQL ALTER TABLE statement to modify database table schema.

Sometimes you need to change the structure of an existing table to meet new business requirements such as adding new columns, removing existing columns… etc. The SQL ALTER TABLE statement allows you to change the structure of existing tables. With the SQL ALTER TABLE statement, you can perform the following operations on a table:

Rename a table.
Add, modify and remove columns
Add table constraints

SQL ALTER TABLE – Rename a table

To rename a table, you use the following syntax:

ALTER TABLE table_name
RENAME TO new_table_nameCode language: SQL (Structured Query Language) (sql)

For example, to change the projects table to proj, we can use the following query:

ALTER TABLE projects
RENAME TO projCode language: SQL (Structured Query Language) (sql)

SQL ALTER TABLE – add new columns

To add new column to a table, we use the following syntax:

ALTER TABLE table_name
ADD new_column datatype(size)Code language: SQL (Structured Query Language) (sql)

For example, to add a new column called status to the proj table, we can perform the following query:

ALTER TABLE proj
ADD status VARCHAR(25)Code language: SQL (Structured Query Language) (sql)

SQL ALTER TABLE – modify columns

We can use SQL ALTER TABLE statement to change column attributes as the syntax below:

ALTER TABLE table_name
MODIFY column_name datatype(size)Code language: SQL (Structured Query Language) (sql)

To modify multiple columns, you use the following syntax:

ALTER TABLE table_name
MODIFY ( column_name1 datatype(size),
         column_name2 datatype(size),
         column_name3 datatype(size),
         ...)Code language: SQL (Structured Query Language) (sql)

For example, to change the data type and default value of the status column in the proj table, you use the following query:

ALTER TABLE proj
MODIFY status SMALLINT NOT NULLCode language: SQL (Structured Query Language) (sql)

SQL ALTER TABLE – remove column

To remove an existing column in a table, you need to specify the table and the column name that you want to remove. The following illustrates the syntax:

ALTER TABLE table_name
DROP COLUMN existing_columnCode language: SQL (Structured Query Language) (sql)

For example, to remove the status column of the proj table, which we have added in the example above, we can use the following query:

ALTER TABLE proj
DROP COLUMN statusCode language: SQL (Structured Query Language) (sql)

When you remove a column, all the data in that column is removed. If you remove a key column, not only the data in that column but also the duplicate rows are removed as well.

The SQL ALTER TABLE statement may have variations in different database systems such as Oracle, MySQL, SQL Server, PostgreSQL…etc You should check the database’s documentation to get a complete knowledge of the statement before using it.

In this tutorial, we have shown you how to change the structure of existing tables using SQL ALTER TABLE statement.