login

Subscribe to SQL Tutorial websiteRSS Feed
 » SQL Tutorial » SQL ALTER TABLE

SQL ALTER TABLE

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

There are some cases that you need to change structure of an existing database table such as add new columns, remove existing columns... SQL provides you ALTER TABLE statement to allow you to modify structure of existing table. With SQL ALTER TABLE statement, you can perform the following actions on a database table:

  • Add a new column or removing an existing column of the table.
  • Modify of maximum length of a column or change column's data type
  • Add or remove default value of a column.
  • Add or remove constraints of table.

To add new column in a table you use the following syntax:

ALTER TABLE table_name
ADD COLUMN new_column datatype(datalength)

First you specify the table you want to add a new column and then you specify new column name, its data type and its maximum length of data.

For example to add new column called availableOnline in the products table in our sample database you can perform the following query:

ALTER TABLE Products
ADD COLUMN availableOnline BIT NOT NULL

To remove an existing column in a database table, you need to specify the table and column name you want to remove. Here is the syntax:

ALTER TABLE table_name
DROP COLUMN existing_column

To drop the column availableOnline in products table you've created in the above example, you can use ALTER TABLE … DROP COLUMN as below:

ALTER TABLE products
DROP COLUMN availableOnline

When you drop a column, all the data in that column in the database table is removed. If you drop a key column which is a part of a composite key of the table, not only the data in the column is removed but also all the duplicated records in that table are removed also.

The SQL ALTER TABLE statement can be different between database products in its additions such as add or remove constraints, set default value… you should check your own database documentation to have a better understanding before you use the query.

In this tutorial, you’ve learned how to modifying table by using SQL ALTER TABLE statement.

Related Tutorials