Changing a Table Definition (ALTER TABLE)

Changing a Table Definition (ALTER TABLE)
The ISO standard provides an ALTER TABLE statement for changing the structure of a
table once it has been created. The definition of the ALTER TABLE statement in the ISO
standard consists of six options to:
n add a new column to a table;
n drop a column from a table;
n add a new table constraint;
n drop a table constraint;
n set a default for a column;
n drop a default for a column.
The basic format of the statement is:
ALTER TABLE TableName
[ADD [COLUMN] columnName dataType [NOT NULL] [UNIQUE]
[DEFAULT defaultOption] [CHECK (searchCondition)]]
[DROP [COLUMN] columnName [RESTRICT | CASCADE]]
[ADD [CONSTRAINT [ConstraintName]] tableConstraintDefinition]
[DROP CONSTRAINT ConstraintName [RESTRICT | CASCADE]]
[ALTER [COLUMN] SET DEFAULT defaultOption]
[ALTER [COLUMN] DROP DEFAULT]
Here the parameters are as defined for the CREATE TABLE statement in the previous
section. A tableConstraintDefinition is one of the clauses: PRIMARY KEY, UNIQUE,
FOREIGN KEY, or CHECK. The ADD COLUMN clause is similar to the definition of a
column in the CREATE TABLE statement. The DROP COLUMN clause specifies the
name of the column to be dropped from the table definition, and has an optional qualifier
that specifies whether the DROP action is to cascade or not:
n RESTRICT The DROP operation is rejected if the column is referenced by another
database object (for example, by a view definition). This is the default setting.
n CASCADE The DROP operation proceeds and automatically drops the column from
any database objects it is referenced by. This operation cascades, so that if a column is
dropped from a referencing object, SQL checks whether that column is referenced by
any other object and drops it from there if it is, and so on.