Oracle – PL-SQL – Foreign key on delete cascade

Oracle, Oracle-Tips and Tricks, Varinder Sandhu

ON DELETE CASCADE:

When a referenced parent table row is removed the entire child are removed automatically.

Example:

-- Create a Table as mytable1 having the PRIMARY KEY
CREATE TABLE MYTABLE1
(
ID INTEGER PRIMARY KEY,
EMP_NAME VARCHAR2(10)
);

-- Create a Table as mytable2 having the FOREIGN KEY
CREATE TABLE MYTABLE2
(
ID INTEGER,
ADDRESS VARCHAR2(50),
FOREIGN KEY (ID) REFERENCES MYTABLE1 (ID)
ON DELETE CASCADE
);

-- Insert one row in mytable1
INSERT INTO MYTABLE1 (ID, EMP_NAME) VALUES (1,'VARINDER');

-- Result
SELECT * FROM MYTABLE1;

-- Insert one row in mytable2
INSERT INTO MYTABLE2 (ID, ADDRESS) VALUES (1,'615 PHASE 3A');

-- Result
SELECT * FROM MYTABLE2;

-- Delete  from table mytable1
DELETE FROM MYTABLE1 WHERE ID = 1;

When a record in the mytable1 table is deleted, all records in the mytable2 table will also be deleted that have the same id value.

Note:

Using “on delete cascade” is dangerous because of possible mistakes and because issuing a single delete on a parent row might invoke thousands of deletes from the child table.

Related Posts

, ,