<< PRIMARY KEY | Requirements for search condition >>

Unique constraints

<< PRIMARY KEY | Requirements for search condition >>

Derby Reference Manual

When you create a primary key constraint, none of the columns included in the primary
key can have NULL constraints; that is, they must not permit NULL values.

ALTER TABLE ADD PRIMARY KEY allows you to include existing columns in a
primary key if they were first defined as NOT NULL. NULL values are not allowed. If the
column(s) contain NULL values, the system will not add the primary key constraint. See

ALTER TABLE statement

for more information.

A table can have at most one PRIMARY KEY constraint.

Unique constraints

A UNIQUE constraint defines a set of columns that uniquely identify rows in a table only
if all the key values are not NULL. If one or more key parts are NULL, duplicate keys are
allowed.

For example, if there is a UNIQUE constraint on

col1

and

col2

of a table, the

combination of the values held by

col1

and

col2

will be unique as long as these values

are not NULL. If one of

col1

and

col2

holds a NULL value, there can be another

identical row in the table.

A table can have multiple UNIQUE constraints.

Foreign key constraints

Foreign keys provide a way to enforce the referential integrity of a database. A foreign
key is a column or group of columns within a table that references a key in some other
table (or sometimes, though rarely, the same table). The foreign key must always include
the columns of which the types exactly match those in the referenced primary key or
unique constraint.

For a table-level foreign key constraint in which you specify the columns in the table that
make up the constraint, you cannot use the same column more than once.

If there is a column list in the ReferencesSpecification (a list of columns in the referenced
table), it must correspond either to a unique constraint or to a primary key constraint
in the referenced table. The ReferencesSpecification can omit the column list for the
referenced table if that table has a declared primary key.

If there is no column list in the ReferencesSpecification and the referenced table has no
primary key, a statement exception is thrown. (This means that if the referenced table
has only unique keys, you must include a column list in the ReferencesSpecification.)

A foreign key constraint is satisfied if there is a matching value in the referenced unique
or primary key column. If the foreign key consists of multiple columns, the foreign key
value is considered NULL if any of its columns contains a NULL.
Note: It is possible for a foreign key consisting of multiple columns to allow one of
the columns to contain a value for which there is no matching value in the referenced
columns, per the SQL-92 standard. To avoid this situation, create NOT NULL constraints
on all of the foreign key's columns.

Foreign key constraints and DML

When you insert into or update a table with an enabled foreign key constraint, Derby
checks that the row does not violate the foreign key constraint by looking up the
corresponding referenced key in the referenced table. If the constraint is not satisfied,
Derby rejects the insert or update with a statement exception.

When you update or delete a row in a table with a referenced key (a primary or unique
constraint referenced by a foreign key), Derby checks every foreign key constraint
that references the key to make sure that the removal or modification of the row does
not cause a constraint violation. If removal or modification of the row would cause a