On Delete Cascade in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

On Delete Cascade in Oracle with Examples

In this article, I am going to discuss On Delete Cascade in Oracle with Examples. Please read our previous article where we discussed Compound Triggers and Arrays to Solve Mutating in Oracle with Examples. At the end of this article, you will learn about the mutating table when we have a foreign key ON DELETE CASCADE.

What is On Delete Cascade in Oracle?

In order to learn about on delete cascade, let us go ahead and create two tables. Let us create the first table by executing the below CREATE TABLE Statement.

CREATE TABLE DEPT1
(
 DEPTNO NUMBER,
 DNAME VARCHAR2(100),
 CONSTRAINT DEPT1_PK PRIMARY KEY (DEPTNO) 
 );

We have created a table named DEPT1. We have two columns DEPTNO, DNAME, and a constraint. This constraint is a primary key which is named DEPT1_PK for the column DEPTNO.

So, the table is created. Let us go ahead and insert records into the table DEPT1 by executing the below INSERT Statements.

INSERT INTO DPET1 (DEPTNO, DNAME) VALUES (1,'HR DEPT');
INSERT INTO DPET1 (DEPTNO, DNAME) VALUES (2,'PO DEPT');
COMMIT;

So, now there is data in the table DEPT1. Let us check if the data is present in the table DEPT1 by executing the below SELECT query.

SELECT * FROM DEPT1;

The data is present. So, Let’s go ahead and create another table that is more detailed with table DEPT1. Please execute the following CREATE TABLE Statement to create the EMP1 table.

CREATE TABLE EMP1
( 
  EMPID NUMBER PRIMARY KEY,
  ENAME VARCHAR2(100),
  DEPTNO NUMBER,
  CONSTRAINT EMP1_FK FOREIGN KEY (DEPTNO) REFERENCES DPET1(DEPTNO) ON DELETE CASCADE
);

Table EMP1 contains EMPID, ENAME, and DEPTNO as columns where there are two constraints. The EMPID is the primary key. EMP1_FK is the foreign key constraint that is referencing the column DEPTNO in the table DEPT1 ON DELETE CASCADE. The meaning of “ON DELETE CASCADE” is if we try to delete master records from the DEPT1 table then oracle will go ahead and delete the child records as well from the EMP1 table. Let’s run this to create the table.

So, the table EMP1 is now created. Let us add some records to the table by executing the below INSERT Statements.

INSERT INTO EMP1 VALUES (1, 'ABC', '1');
INSERT INTO EMP1 VALUES (2, 'DEF', '1');
INSERT INTO EMP1 VALUES (3, 'GHI', '1');
INSERT INTO EMP1 VALUES (4, 'JKL', '2');
INSERT INTO EMP1 VALUES (5, 'MNO', '2');
COMMIT;

So, now the data is inserted. Let’s check how the data is arranged in the table.

So, we have 3 employees working in dept 1 and 2 employees working in dept 2. Now, let’s try to delete a record by executing the below DELETE Statement and see how the database reacts. Because here we have a constraint connected with the dept 1 column and the employee details.

DELETE FROM DEPT1 WHERE DEPTNO=1;

So, the record is now deleted. We have deleted the records with the deptno as 1. Let us try to check the employee details by executing the following SELECT Statement.

SELECT * FROM EMP1;

So, the records with the deptno as 1 are also deleted. This is not what we required. We need to create a trigger to showcase the ON DELETE CASCADE with the trigger. So, create the trigger by executing the below SQL Statements.

create or replace trigger EMP1_test
before
delete
on EMP1
for each row
declare
minv number;
begin
    select min(EMPID)
    into minv
    from emp1;
end;

From the above trigger we have given the trigger name EMP1_test and the event and timing is before delete on the table EMP1. In order not to delete the records in the table we are creating a mutating error. So, to create a mutating table error we are just using a select statement to run it on the table EMP1. So, while deleting the record oracle also runs the trigger on the table EMP1 and the same time runs the select statement while deleting the record. This situation will create a mutating error.

So, we are just declaring a new variable and then storing the minimum value of the emp1 table into the variable minv. Let’s try to compile this trigger.

The trigger is now compiled. Let’s try to check the trigger and see how the trigger works. We will be deleting the record with deptno as 2 by executing the below DELETE Statement.

DELETE FROM DEPT1 WHERE DEPTNO=2;

We are facing the mutating table issue. So, we understood that if we have a trigger and on delete cascade, we may face the mutating table issue.

In the next article, I am going to discuss DDL Triggers in Oracle with Examples. Here, in this article, I try to explain On Delete Cascade in Oracle with Examples. I hope you enjoy this Compound Trigger and Array to Solve Mutating in Oracle with Examples article.