constraintCharacteristics

constraintCheckTime [ [ NOT ] DEFERRABLE ] |
[ NOT ] DEFERRABLE [ constraintCheckTime ]

The constraintCheckTime is defined as follows:

INITIALLY DEFERRED | INITIALLY IMMEDIATE

If DEFERRABLE is specified, the constraint is deferrable; otherwise it is not deferrable unless INITIALLY DEFERRED is specified. To make a constraint from an existing database deferrable, you must drop and recreate the constraint.

If constraintCheckTime is not specified, INITIALLY IMMEDIATE is implicit.

If INITIALLY DEFERRED is specified and DEFERRABLE is not specified, DEFERRABLE is implicit. If INITIALLY DEFERRED is specified, NOT DEFERRABLE is not permitted.

The deferrability or the constraintCheckTime (that is, the default checking time) of a constraint cannot be altered. To change these characteristics, you must drop the constraint and recreate it.

NOT NULL constraints are not deferrable; all others are deferrable. The NOT NULL constraint can, however, be dropped and recreated if desired. This will require a full table scan.

A constraint can be specified as DEFERRABLE or NOT DEFERRABLE, or with a constraintCheckTime of INITIALLY DEFERRED or INITIALLY IMMEDIATE, only after a database has been fully upgraded to Derby Release 10.11 or higher. (See "Upgrading a database" in the Derby Developer's Guide for more information.) These keywords have no meaning in a database that is at Release 10.10 or lower.

After a full upgrade to Release 10.11 or higher, old constraints on the database will be converted to NOT DEFERRABLE, the default value.

Note: Deferred constraints sometimes impose extra performance overhead to allow for the deferred checking. If your application does not require deferred checking, we recommend that you make constraints NOT DEFERRABLE (the default).

Note: In contrast to constraint checking, the referential actions specified by a referential constraint are never deferred. In Derby, these actions are RESTRICT, SET NULL and CASCADE for delete and RESTRICT for update. If NO ACTION is specified, the referential check can be deferred.