SQL ALTER TABLE Statement

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

The ALTER TABLE statement is also used to add and drop various constraints on an existing table.

The SQL ALTER TABLE Statement

ALTER TABLE - ADD Column

To add a column in a table, use the following syntax:

Demo Employee Table

alter table

Example

 ALTER TABLE table_name

 ADD column_name datatype

You can click on above box to edit the code and run again.

The following SQL adds an "Email" column to the "employee" table:

Example

 ALTER TABLE employee

 ADD Email varchar(255);

You can click on above box to edit the code and run again.

ALTER TABLE - DROP COLUMN

To delete a column in a table, use the following syntax (notice that some database systems don't allow deleting a column):

Example

  ALTER TABLE table_name

  DROP COLUMN column_name;

You can click on above box to edit the code and run again.

The following SQL deletes the "Email" column from the "employee" table:

Example

 ALTER TABLE employee

 DROP COLUMN Email;

You can click on above box to edit the code and run again.

ALTER TABLE - RENAME COLUMN

To rename a column in a table, use the following syntax:

Example

 ALTER TABLE table_name

 RENAME COLUMN old_name  TO new_name ;

You can click on above box to edit the code and run again.

ALTER TABLE - ALTER/MODIFY DATATYPE

To change the data type of a column in a table, use the following syntax:

Example

SQL Server / MS Access:


 ALTER TABLE table_name

 ALTER COLUMN column_name datatype ;

You can click on above box to edit the code and run again.

Example

My SQL / Oracle (prior version 10G):


 ALTER TABLE table_name

 MODIFY COLUMN column_name datatype ;

You can click on above box to edit the code and run again.

Example

Oracle 10G and later:


 ALTER TABLE table_name

  MODIFY column_name datatype ;

You can click on above box to edit the code and run again.