O'Reilly logo

SQL in a Nutshell by Kevin Kline

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required