<< column-alteration | Dropping constraints >>

Adding constraints

<< column-alteration | Dropping constraints >>

Derby Reference Manual

The syntax for the

column-definition

for a new column is almost the same as for a column

in a CREATE TABLE statement. This syntax allows a column constraint to be placed on
the new column within the ALTER TABLE ADD COLUMN statement. However, a column
with a NOT NULL constraint can be added to an existing table if you give a default value;
otherwise, an exception is thrown when the ALTER TABLE statement is executed.

Just as in CREATE TABLE, if the column definition includes a primary key constraint,
the column cannot contain null values, so the NOT NULL attribute must also be specified
(SQLSTATE 42831).

Note: If a table has an UPDATE trigger without an explicit column list, adding a column
to that table in effect adds that column to the implicit update column list upon which the
trigger is defined, and all references to transition variables are invalidated so that they
pick up the new column.

If you add a generated column to a table, Derby computes the generated values for all
existing rows in the table.

Adding constraints
ALTER TABLE ADD CONSTRAINT adds a table-level constraint to an existing table.
Any supported table-level constraint type can be added via ALTER TABLE. The following
limitations exist on adding a constraint to an existing table:

· When adding a foreign key or check constraint to an existing table, Derby checks

the table to make sure existing rows satisfy the constraint. If any row is invalid,
Derby throws a statement exception and the constraint is not added.

· All columns included in a primary key must contain non null data and be unique.

ALTER TABLE ADD UNIQUE or PRIMARY KEY provide a shorthand method of
defining a primary key composed of a single column. If PRIMARY KEY is specified
in the definition of column C, the effect is the same as if the PRIMARY KEY(C)
clause were specified as a separate clause. The column cannot contain null values,
so the NOT NULL attribute must also be specified.

For information on the syntax of constraints, see

CONSTRAINT clause

. Use the

syntax for table-level constraint when adding a constraint with the ADD TABLE ADD
CONSTRAINT syntax.

Dropping columns

ALTER TABLE DROP COLUMN allows you to drop a column from a table.

The keyword COLUMN is optional.

The keywords CASCADE and RESTRICT are also optional. If you specify neither
CASCADE nor RESTRICT, the default is CASCADE.

If you specify RESTRICT, then the column drop will be rejected if it would cause a
dependent schema object to become invalid.

If you specify CASCADE, then the column drop should additionally drop other schema
objects which have become invalid.

The schema objects which can cause a DROP COLUMN RESTRICT to be rejected
include: views, triggers, primary key constraints, foreign key constraints, unique key
constraints, check constraints, and column privileges. If one of these types of objects
depends on the column being dropped, DROP COLUMN RESTRICT will reject the
statement.

Derby also raises an error if you specify RESTRICT when you drop a column referenced
by the generation-clause of a generated column. However, if you specify CASCADE, the
generated column is also dropped with CASCADE semantics.

You may not drop the last (only) column in a table.