9. SQL Reference

9.12. ALTER TABLE Statement

add_column:
            ALTER TABLE q_table_name ADD opt_col_add_column add_col_column_def_list
          | ALTER TABLE q_table_name DROP opt_col_add_column add_col_column_list
          | ALTER TABLE q_table_name MODIFY opt_col_add_column column_def

add_col_column_def_list: column_def
                       | add_col_column_def_list ',' column_def

add_col_column_list: column
                   | add_col_column_list ',' column

table_rename:
            ALTER TABLE q_table_name RENAME new_table_name

alter_constraint:
            ALTER TABLE q_table_name constraint_op opt_table_constraint_def

constraint_op: ADD
             | DROP
             | MODIFY

opt_drop_behavior: /* empty */
                 | CASCADE
                 | RESTRICT

opt_table_constraint_def: CONSTRAINT identifier opt_drop_behavior
                        | table_constraint_def

view_query_spec: query_exp
               | query_no_from_spec

alter_table
	: ALTER TABLE q_table_name ADD opt_col_add_column add_col_column_def_list

	| ALTER TABLE q_table_name DROP opt_col_add_column add_col_column_list

	| ALTER TABLE q_table_name MODIFY opt_col_add_column column_def

	| ALTER TABLE q_table_name RENAME new_table_name

	| ALTER TABLE <table> ADD | DROP | MODIFY <constraint> [<constraint-name>]

<constraint> ::=
	: PRIMARY KEY '(' <column list> ')'
	| FOREIGN KEY '(' <column list> ')' <references>
	| CHECK ( search-condition )

<references> ::=
   REFERENCES <table> ['(' <column list> ')' ] <OPTIONS>

<OPTIONS>  ::=
   [ON UPDATE OPT_ACTION] [ON DELETE OPT_ACTION]

The ALTER TABLE statement adds or drops columns and renames tables.

Adding or dropping a column of a table will create a new version of the table's schema entry. The rows of the altered table will be changed to conform to the new definition when next updated. All newly inserted rows will be in the new row layout. This means that ALTER TABLE itself executes in fixed time without locking the table. The time to update the actual data will be spread over subsequent updates.

An added column will have a NULL value on those rows where the new column has not been set. A possible default will only apply to newly inserted rows.

When dropping a column one can execute a statement of the form UPDATE <table> SET <key> = <key> to force the physical change, causing space associated with the dropped column to be freed.

The column_def in the ADD clause may specify any column options, except PRIMARY KEY.

If the table name is not fully qualified it is completed as in any table reference. The new name in the RENAME clause is defaulted to the current qualifier and user account, as in a CREATE TABLE.

A primary key may only be modified, never dropped or added. Tables always have exactly one primary key.

The first unique index of an empty table becomes the primary key. This may thereafter not be dropped.

The primary key of an attached table may not be changed.

Foreign keys can be added. Dropping a foreign key will drop all foreign keys between the foreign key and primary key tables.

ALTER TABLE cannot be applied to an attached table.

9.12.1. Adding a CHECK Constraint

A CHECK constraint can be added to a table after it has been created and populated providing that none of the tables contents would violate the constraint.