Examples of the Single-Column Constraint format

These examples illustrate single-column constraint format options to define a foreign-key constraint that is enabled by default, and to declare the name of a disabled referential constraint.

A referential constraint enabled by default

The following example uses the single-column constraint format to define a referential relationship between the sub_accounts and accounts tables. (The terms foreign-key constraint and referential constraint are synonyms.) The ref_num column (the foreign key) in the sub_accounts table references the acc_num column (the primary key) in the accounts table.

CREATE TABLE accounts (
   acc_num INTEGER PRIMARY KEY,
   acc_type INTEGER,
   acc_descr CHAR(20));
CREATE TABLE sub_accounts (
   sub_acc INTEGER PRIMARY KEY,
   ref_num INTEGER REFERENCES accounts (acc_num),
   sub_descr CHAR(20));

The single-column constraint format syntax of the CREATE TABLE statement above that defines the sub_accounts table does not explicitly specify that the ref_num column is a foreign key, but the REFERENCES keyword specifies that ref_num must have the same value as the acc_num column in some row of the accounts table. This implies that the ref_num column is the foreign key in a referential relationship in which sub_accounts is the referencing table, and accounts is the referenced table.

In single-column constraint format, you do not explicitly specify that the ref_num column is a foreign key. To include the FOREIGN KEY keywords when you place a referential constraint on a single column (or on a list of columns that reference the same primary key) of the referencing table, you must instead use the multiple-column constraint format syntax to define the referential constraint.

By default, this constraint on the sub_accounts table is enabled without filtering, because no explicit constraint mode is specified. You can use the neither the DISABLED or FILTERING keyword is specified in the example. The SET CONSTRAINTS option to the SET Database Object Mode statement can reset the object mode of existing constraints.

Because the sub_accounts example above declares no name for the referential constraint, the database server generates an implicit identifier when it registers this constraint in the sysconstraints system catalog table, and registers its mode ( E ) in the sysobjstate system catalog table.

A disabled referential constraint

The next CREATE TABLE statement creates a xeno_counts table, and defines a referential constraint between its xeno_num column and the acc_num column in the accounts table from the first example. This single-column constraint format syntax also includes a constraint definition, specifying DISABLED as its constraint mode, and declaring xeno_constr as the name of this foreign-key constraint. Here xeno_accounts is the referencing table, and accounts is the referenced table.

CREATE TABLE xeno_counts (
   xeno_acc INTEGER PRIMARY KEY,
   xeno_num INTEGER REFERENCES accounts (acc_num)
      CONSTRAINT xeno_constr DISABLED,
   xeno_descr CHAR(20));

In DISABLED mode, the xeno_constr constraint is not enforced when DML operations produce violating rows in the xeno_counts table. To enforce referential integrity, however, you can use the SET CONSTRAINTS option to the SET Database Object Mode statement to change the constraint mode to ENABLED. Alternatively, SET CONSTRAINTS can reset the xeno_constr constraint to a FILTERING mode, after the START VIOLATIONS statement associates a violations table with the xeno_counts table.