<< Unique constraints | Referential actions >>

Requirements for search condition

<< Unique constraints | Referential actions >>

Derby Reference Manual

constraint violation, the update or delete is not permitted and Derby throws a statement
exception.

Derby performs constraint checks at the time the statement is executed, not when the
transaction commits.

Backing indexes

UNIQUE, PRIMARY KEY, and FOREIGN KEY constraints generate indexes that
enforce or "back" the constraint (and are sometimes called backing indexes). PRIMARY
KEY constraints generate unique indexes. FOREIGN KEY constraints generate
non-unique indexes. UNIQUE constraints generate unique indexes if all the columns
are non-nullable, and they generate non-unique indexes if one or more columns are
nullable. Therefore, if a column or set of columns has a UNIQUE, PRIMARY KEY, or
FOREIGN KEY constraint on it, you do not need to create an index on those columns for
performance. Derby has already created it for you. See

Indexes and constraints

These indexes are available to the optimizer for query optimization (see

CREATE INDEX

statement

) and have system-generated names.

You cannot drop backing indexes with a DROP INDEX statement; you must drop the
constraint or the table.

Check constraints

A check constraint can be used to specify a wide range of rules for the contents of
a table. A search condition (which is a boolean expression) is specified for a check
constraint. This search condition must be satisfied for all rows in the table. The search
condition is applied to each row that is modified on an INSERT or UPDATE at the time of
the row modification. The entire statement is aborted if any check constraint is violated.

Requirements for search condition

If a check constraint is specified as part of a column-definition, a column reference
can only be made to the same column. Check constraints specified as part of a table
definition can have column references identifying columns previously defined in the
CREATE TABLE statement.

The search condition must always return the same value if applied to the same values.
Thus, it cannot contain any of the following:

· Dynamic parameters (?)
· Date/Time Functions (CURRENT_DATE, CURRENT_TIME,

CURRENT_TIMESTAMP)

· Subqueries
· User Functions (such as USER, SESSION_USER, CURRENT_USER)

Referential actions

You can specify an ON DELETE clause and/or an ON UPDATE clause, followed by the
appropriate action (CASCADE, RESTRICT, SET NULL, or NO ACTION) when defining
foreign keys. These clauses specify whether Derby should modify corresponding foreign
key values or disallow the operation, to keep foreign key relationships intact when a
primary key value is updated or deleted from a table.

You specify the update and delete rule of a referential constraint when you define the
referential constraint.

The update rule applies when a row of either the parent or dependent table is updated.
The choices are NO ACTION and RESTRICT.

When a value in a column of the parent table's primary key is updated and the update
rule has been specified as RESTRICT, Derby checks dependent tables for foreign