SQL Extensions   «Prev 

FOREIGN KEY constraint syntax

[CONSTRAINT constraintname] FOREIGH KEY (column list)
REFERENCES [user.]table[(column list)]
[ON DELETE {CASCADE | SET NULL} ]

CREATE TABLE PET_CARE_LOG
(PRODUCT_ID NUMBER(10),
LOG_DATETIME DATE,
LOG_TEXT VARCHAR2(500),
CONSTRAINT PET_PRODUCT_FK FOREIGN KEY(PRODUCT_ID)
 REFERENCES PRODUCT(PRODUCT_ID) ON DELETE CASCADE)

column list 1 This list is columns in the current table that make up the foreign key columns.
column list 2 This list of columns is the primary key columns in the referenced table. You can leave this off if you wish. If left off, Oracle simply assumes that the entire primary key in its original order will be used.
ON DELETE{CASCADE If you include the ON DELETE CASCADE parameter, this means that when the parent row in the referenced table is deleted, the child rows in this table are deleted.
SET NULL If you include the ON DELETE SET NULL parameter, when a parent row is deleted, the child rows in this table have their foreign key data set to null values.
] Eliminating the ON DELETE parameter means that the parent row cannot be deleted if there are any child rows in this table.