SQL in a Nutshell

Name

ALTER TABLE

Synopsis

The ALTER TABLE statement allows an existing table to be modified without dropping the table or altering existing permissions on the table. In this way, certain incremental changes are performed easily on an existing table.

Both Oracle and Microsoft SQL Server support this command with a number of variations to service their differing physical file-allocation methods.

Vendor	Command
SQL Server	Supported, with variations
MySQL	Supported, with limitations
Oracle	Supported, with variations
PostgreSQL	Supported, with variations

SQL99 Syntax and Description

ALTER TABLE table_name
[ADD [COLUMN] column_name datatype attributes]
| [ALTER [COLUMN] column_name SET DEFAULT default_value]
| [ALTER [COLUMN] column_name DROP DEFAULT]
| [ALTER [COLUMN] column_name ADD SCOPE table_name
| [ALTER [COLUMN] column_name DROP SCOPE {RESTRICT | CASCADE}]
| [DROP [COLUMN] column_name {RESTRICT | CASCADE}]
| [ADD table_constraint_name]
| [DROP CONSTRAINT table_constraint_name {RESTRICT | CASCADE}]

The SQL99 ALTER TABLE statement allows many useful modifications to be made to an existing table. This versatile command allows users to ADD COLUMN or table constraint; add or drop a DEFAULT; add or drop SCOPE on columns that are set up to reference a user-defined type; and DROP both a column and a table constraint. DROP RESTRICT tells the host DBMS to abort the command if it sees that other objects in the database depend on the column or table constraint. DROP CASCADE tells the ...