Foreign Key (References) Specification

forkey_def:
        FOREIGN KEY (column_name [, column_name]...)
            REFERENCES table_name[ (column_name [, column_name]...)]
            [ORDER BY (column_name [ASC | DESC] [, column_name [ASC | DESC]]...)]
            [triggered_action [triggered_action]]

A column that is declared with the REFERENCES clause identifies it as a FOREIGN KEY column referencing the PRIMARY KEY column in the referenced table, table_name. This means that there must exist a row in the referenced table with a PRIMARY KEY value that matches the column value being assigned by the INSERT or UPDATE statement.

Foreign key references are automatically implemented by RDM SQL for quick access and maintenance of referential integrity. A triggered_action can be specified with foreign key columns in order to indicate what should happen when the referenced row is updated or deleted. The default action is RESTRICT meaning that PRIMARY KEY rows that have existing foreign key references cannot be updated or deleted. If ON ... CASCADE is specified, then all of the referenced rows are updated or deleted when the PRIMARY KEY row is updated (i.e., the primary key column value) or deleted.

Note that the referencing table may itself have a PRIMARY KEY declared that is referenced by foreign keys in other tables that may not have a CASCADE triggered action specified. Thus, a delete of the referenced row of a cascade-delete-allowed table may be denied due to a restrict foreign key on a row of a referencing table. If ON ... SETNULL is specified, then all of the referencing foreign key columns will be set to NULL. This option is not allowed when the foreign key column has been declared as NOT NULL.