SQL ALTER TABLE

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

SQL ALTER TABLESometimes 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:

SQL ALTER TABLE – Rename a table

To rename a table, you use the following syntax:

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

SQL ALTER TABLE – add new columns

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

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

SQL ALTER TABLE – modify columns

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

To modify multiple columns, you use the following syntax:

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

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:

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:

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.