You can not only create and remove a database table but also modify an existing database table. SQL provides you ALTER TABLE statement to allow you to do so. With SQL ALTER TABLE statement you can do the following things on the database table:
- Add a new column or removing an existing column of the table.
- Modify of maximum length of a column.
- 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 column name, its data type and its maximum length of data. For example to add new column called availableOnline in the products table 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 we created in the above example we can use ALTER TABLE … DROP COLUMN to do so. Here is the query:
ALTER TABLE products
DROP COLUMN availableOnline
When you drop a column, all the data in that column 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 can check it out to know more in details.
In this tutorial, you’ve learnt how to modifying table by using SQL ALTER TABLE statement. You’ve learnt how to add or removing a column from a database table.