SQL Extensions   «Prev 

FOREIGN KEY in Column specification

This is a variation on the syntax for creating a FOREIGN KEY constraint. It has several restrictions:
  1. You cannot name the foreign key yourself, you must allow the system to name it.
  2. The foreign key column must be one column.

Here is the syntax:
REFERENCES [user.]table [(column_name)] 
[ ON DELETE { CASCADE | SET NULL} ]

Here is an example:
CREATE TABLE PET_CARE_LOG
(PRODUCT_ID NUMBER(10) REFERENCES PRODUCT 
 ON DELETE SET NULL, 
LOG_DATETIME DATE, 
LOG_TEXT VARCHAR2(500)) 

In this example, the PRODUCT_ID is a foreign key referencing the PRODUCT table. When the parent row (in PRODUCT) is deleted, the child row's PRODUCT_ID column is set to null.