ALTER TABLE

Synopsis

Use the ALTER TABLE statement to change the definition of an existing table.

Syntax

• Grammar
• Diagram

alter_table ::= ALTER TABLE [ ONLY ] name [ * ] alter_table_action 
                [ , ... ]

alter_table_action ::= ADD [ COLUMN ] column_name data_type
                       | RENAME TO table_name
                       | DROP [ COLUMN ] column_name 
                         [ RESTRICT | CASCADE ]
                       | ADD alter_table_constraint
                       | DROP CONSTRAINT constraint_name 
                         [ RESTRICT | CASCADE ]
                       | RENAME [ COLUMN ] column_name TO column_name

alter_table_constraint ::= [ CONSTRAINT constraint_name ] 
                           { CHECK ( expression )
                             | UNIQUE ( column_names ) 
                               index_parameters
                             | FOREIGN KEY ( column_names ) 
                               references_clause }

Semantics

alter_table

ALTER TABLE [ ONLY ] name [ * ] alter_table_action [ , … ]

Alter the specified table and dependencies.

• ONLY — Limit the change to the specified table.

alter_table_action

Specify one of the following actions.

ADD [ COLUMN ] column_name data_type

Add the specified column with the specified data type.

RENAME TO table_name

Rename the table to the specified table name.

DROP [ COLUMN ] column_name [ RESTRICT | CASCADE ]

Drop the named column from the table.

• RESTRICT — Remove only the specified

ADD alter_table_constraint

Add the specified constraint to the table. For descriptions of valid table_constraint values, see CREATE TABLE.

DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]

Drop the named constraint from the table.

• RESTRICT — Remove only the specified constraint.
• CASCADE — Remove the specified constraint and any dependencies.

RENAME [ COLUMN ] column_name TO column_name

Rename a column to the specified name.

alter_table_constraint

Specify a table constraint.

CONSTRAINT constraint_name

Specify the name of the constraint.

CHECK ( expression ) | FOREIGN KEY ( column_names ) reference_clause

See also

• CREATE TABLE