REFERENCES Clause

This syntax fragment is part of the Single-Column Constraint Format.
(1)
REFERENCES Clause

1  REFERENCES table?  ( + , column )? 1 ON DELETE CASCADE
Notes:
  • 1 HCL OneDB™ extension
Element Description Restrictions Syntax
column Referenced column in the referenced table See Restrictions on Referential Constraints. Identifier
table The referenced table The referenced and the referencing tables must reside in the same database Identifier

The REFERENCES clause allows you to place a foreign-key constraint on one or more columns. The referenced column can be in the same table as the referencing column, or in a different table in the same database.

For example, the following example declares a disabled foreign-key constraint called detail_fork1 on the detail table. 
CREATE TABLE master (col_one INT, col_two CHAR);
CREATE TABLE detail (col_one INT, col_twain CHAR);
. . .  
ALTER TABLE detail ADD CONSTRAINT (FOREIGN KEY ( col_one )
   REFERENCES master( col_one ) CONSTRAINT detail_fork1 DISABLED);
In DISABLED mode, detail_fork1 has no effect, but if it were subsequently enabled, it would restrict INSERT and UPDATE operations on the detail table to rows whose column col_one values matched existing values in column col_one of the referenced master table. In the ALTER TABLE statement above, the parentheses (FOREIGN KEY . . . DISABLED) that delimit the new referential constraint definition are optional.

If the referenced table is different from the referencing table, the default column is the primary-key column. If the referenced table is the same as the referencing table, there is no default.

The optional ON DELETE CASCADE keywords can either be specified the last keywords in the REFERENCES clause, or they can follow the declaration of the constraint name in the Constraint definition.

For more information on the effects of these keywords in DELETE operations, see Using the ON DELETE CASCADE Option.