4D Doc Center : foreign_key

Home

4D v14

foreign_key_definition



	Command foreign_key_definitioCONSTRAINT sql_name FOREIGN KEY (column_reference, ... , column_reference) REFERENCES sql_name [(column_reference, ... , column_reference)] [ON DELETE {RESTRICT \|CASCADE}] [ON UPDATE {RESTRICT \|CASCADE}]

Description

A foreign_key_definition is used to match the primary key fields (column_reference) set in another table in order to ensure data integrity. The FOREIGN KEY constraint is used to pass the one or more column references (column_reference) to be defined as the foreign keys (which match the primary keys of another table).

The CONSTRAINT sql_name clause is used to name the FOREIGN KEY constraint.

The REFERENCES clause that follows is used to specify the matching primary key field sources in another table (sql_name). You can omit the list of column_reference type arguments if the table (sql_name) specified in the REFERENCES clause has a primary key that is to be used as the matching key for the foreign key constraint.

The optional ON DELETE CASCADE clause specifies that when a row is deleted from the parent table (containing the primary key fields), it is also removed from any rows associated with that row in the child table (containing the foreign key fields). Passing the optional ON DELETE RESTRICT clause prevents any data from being deleted from a table if any other tables reference it.

The optional ON UPDATE CASCADE clause specifies that whenever a row is updated in the parent table (containing the primary key fields), it is also updated in any rows associated with that row in the child table (containing the foreign key fields). Passing the optional ON UPDATE RESTRICT clause prevents any data from being updated in a table if any other tables reference it.
Note that if both the ON DELETE and ON UPDATE clauses are passed, they must both be of the same type (e.g. ON DELETE CASCADE with ON UPDATE CASCADE, or ON DELETE RESTRICT with ON UPDATE RESTRICT).
If neither the ON DELETE nor the ON UPDATE clause is passed, then CASCADE is used as the default rule.

Example

This example creates the ORDERS table then sets the Customer_SID column as the foreign key, associated with the SID column of the CUSTOMERS table:

CREATE TABLE ORDERS
(Order_ID INT32,
Customer_SID INT32,
Amount NUMERIC,
PRIMARY KEY (Order_ID),
CONSTRAINT fk_1 FOREIGN KEY (Customer_SID) REFERENCES CUSTOMER(SID));