Oracle allows different types of referential integrity actions to be enforced, as specified with the definition
of a FOREIGN KEY constraint:
The UPDATE/DELETE No Action Restriction This action prevents the update or deletion of a parent key
if there is a row in the child table that references
the key. By default, all FOREIGN KEY constraints enforce the no action restriction; no option needs to be
specified when defining the constraint to enforce the
no action restriction. For example:
CREATE TABLE Emp_tab (
FOREIGN KEY (Deptno) REFERENCES Dept_tab);
The ON DELETE CASCADE Action This action allows data that references the parent key to be deleted (but
not updated). If referenced data in the
parent key is deleted, all rows in the child table that depend on the deleted parent key values are also deleted.
To specify this referential action, include the ON
DELETE CASCADE option in the definition of the FOREIGN KEY constraint. For example:
CREATE TABLE Emp_tab (
FOREIGN KEY (Deptno) REFERENCES Dept_tab
ON DELETE CASCADE);
The ON DELETE SET NULL Action This action allows data that references the parent key to be deleted
(but not updated). If referenced data in the
parent key is deleted, all rows in the child table that depend on the deleted parent key values have their
foreign keys set to null. To specify this referential
action, include the ON DELETE SET NULL option in the definition of the FOREIGN KEY constraint. For example:
CREATE TABLE Emp_tab (
FOREIGN KEY (Deptno) REFERENCES Dept_tab
ON DELETE SET NULL);
Enabling FOREIGN KEY Integrity Constraints
FOREIGN KEY integrity constraints cannot be enabled if the referenced primary or unique key's constraint is not
present or not enabled.
Listing Integrity Constraint Definitions
The data dictionary contains the following views that relate to integrity constraints:
ALL_CONSTRAINTS
ALL_CONS_COLUMNS
CONSTRAINT_COLUMNS
CONSTRAINT_DEFS
USER_CONSTRAINTS
USER_CONS_COLUMNS
USER_CROSS_REFS
DBA_CONSTRAINTS
DBA_CONS_COLUMNS
DBA_CROSS_REFS