SQL-J Language Reference
Page 48 of 121

SET CONSTRAINTS statement

The SET CONSTRAINTS statement allows you to temporarily disable or re-enable a specific foreign key or check constraint or constraints, all foreign key or check constraints in a table, or all foreign key and check constraints in a database. In addition, you are allowed to specify a primary key or unique constraint. Disabling a foreign key or check constraint actually disables the constraint. "Disabling" a primary or unique constraint disables all the foreign keys that reference that constraint and does not actually turn off the primary or unique constraint.

Syntax

SET CONSTRAINTS
{
    ALL |
     ConstraintName [, ConstraintName ]* |
    FOR TableName
}
{ ENABLED | DISABLED }

Re-enabling a constraint requires that every row in the table meet the constraint condition. If a row violates the constraint, the constraint remains in DISABLED mode, and an exception is thrown. If a single SET CONSTRAINTS command affects more than one constraint and any single constraint fails, no constraints are re-enabled, and an exception is thrown.

SET CONSTRAINTS Examples

-- turn off the foreign-key and check constraints
-- in the HotelAvailability table
SET CONSTRAINTS
FOR HotelAvailability
DISABLED

-- turn them all back on
SET CONSTRAINTS
ALL
ENABLED

-- turn off all foreign keys that reference the Cities
-- primary key
-- CITIES_PK will be marked as disabled in
-- the SYS.SYSCONSTRAINTS system table
SET CONSTRAINTS
CITIES_PK
DISABLED

The state of a constraint (whether it is enabled or disabled) is reflected in the STATE column of SYS.SYSCONSTRAINTS. Primary and unique keys that have been "disabled" are marked disabled in this table, even though only foreign keys that reference them are actually disabled.