login

Subscribe to SQL Tutorial website RSS Feed
 » SQL Data Definition » ALTER TABLE Statement

SQL ALTER TABLE Statement

You've created tables and working with them for a while. At a time when functions of database application changes; you may find that the tables in your database is not right for the new situation and you want to change it. SQL ALTER TABLE statement can be used to change structure of an existing table you have in database. With SQL ALTER TABLE statement you can add, drop one or more columns from a table or even change column's definition of every columns Almost database products allows you to change the table even when the table have had already data in it. Here is the syntax of SQL ALTER TABLE statement:

ALTER TABLE table_name  
{[ADD [COLUMN] column_definition] |   
[ALTER [COLUMN] column_name
{SET DEFAULT default | DROP DEFAULT}] |   
[DROP [COLUMN] column_name
	RESTRICT | CASCADE] |   
[ADD table_constraint] |   
[DROP CONSTRAINT constraint_name
	RESTRICT | CASCADE]  };

Here are examples of how to add a new column which name is hiredate into employees table and drop column salary from it:

ALTER TABLE employees   
ADD COLUMN hiredate DATE NOT NULL  
AFTER salary

ALTER TABLE employees   
DROP COLUMN salary

You can even also change the size of the column in the table. You can make a column larger or smaller. When you make a column larger, the database server does not have any problem with it because all existing data can convert without losses. But when you make it smaller (which is unusual) you should take care about it. Some database products allow you to do it when the data in the table is empty or all data in the column has size less than the size you are changing to. Here is an example of changing the size of column name in the employees table from 255 (its original size) to 50:

ALTER TABLE employees
ALTER COLUMN name VARCHAR(50)
Read On