Specifying Referential Actions for Foreign Keys
Oracle permits referential integrity actions to be enforced and as specified the definition of a FOREIGN KEY constraint is as follows:
•The ON DELETE CASCADE Action This action permits referenced data in the parent key to be deleted (but not updated). If in the parent key referenced data is deleted, all rows in the child table which depend on the deleted parent key values are also deleted. To specify this referential action, involve the ON DELETE CASCADE option in the definition of the FOREIGN KEY constraint. Instance example is given below:
Example
CREATE TABLE emp (. . .,
FOREIGN KEY (deptno) REFERENCES dept
ON DELETE CASCADE);
In this given example when a foreign key is added with an ON DELETE CASCADE constraint and the related record in the master table dept is checked. It means that, whenever a record in dept table is deleted and corresponding record in the emp table is also deleted.
Example
Let’s Consider the following CREATE TABLE statements which define a number of integrity constraints, and the following example are as given below:
CREATE TABLE dept (
deptno NUMBER(3) PRIMARY KEY, # defining a primary key#
dname VARCHAR2(15),
loc VARCHAR2(15),
CONSTRAINT dname_ukey UNIQUE (dname, loc), # defining a unique key with dname_ukey#
CONSTRAINT loc_check1
CHECK (loc IN ('NEW YORK', 'BOSTON', and ‘CHICAGO’)));# define a number of integrity constraints#
CREATE TABLE emp (
empno NUMBER(5) PRIMARY KEY,
ename VARCHAR2(15) NOT NULL,
job VARCHAR2(10),
mgr NUMBER(5) CONSTRAINTmgr_fkey
REFERENCES emp ON DELETE CASCADE,
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(5,2),
deptno NUMBER(3) NOT NULL CONSTRAINT
dept_fkey REFERENCES dept);
This creates the 2 table namely the primary key along with other constraints are specified. In the emp table with foreign key on delete cascade is also specified which automatically ensures that when a record in Dept table is deleted corresponding rows of the table are also deleted.