What is Cascade delete in Oracle with foreign key constraint?

A foreign key with cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table will automatically be deleted. This is called a cascade delete in Oracle. See the e.g below:

Master Table:-

Create Table Emp

(Id Number(10),

Name Varchar2(100),

Constraint PK_Emp_Id Primary Key(Id));

Child Table:-

Create Table Emp_Salary

(Id Number(10),

EmpId Number(10),

Salary decimal(5,2),

Constraint PK_EMP_Salary_ID Primary Key(Id),

Constraint FK_EMP_Salary_EmpId Foreign Key (Empid) References Emp(Id) ON DELETE CASCADE

);

Insert Record into Master table and Child table both:-

Insert into Emp (Id,Name) Values (1,'Jon');

Insert into Emp (Id,Name) Values (2,'Paul');

Insert Into Emp_Salary (Id,EmpId,Salary) Values (101,1,'100.01');

Insert Into Emp_Salary (Id,EmpId,Salary) Values (102,2,'200.01');

Commit;

Check the records from both tables:-

Query For Master table:- Select  Id, Name from Emp;

Query For Child table :- Select Id, EmpId, Salary from Emp_Salary;

O/P from both tables:-

Emp			Emp_Salary
Id	Name		Id	EmpId	Salary
1	Jon		101	1	100.01
2	Paul		102	2	200.01

Now delete the record from master table and child record will be automatically deleted because we have created foreign key constraint with cascade delete.

Query:- Delete From Emp Where Id=2; Commit;

Again check the record from both tables

Query For Master table:- Select  Id, Name from Emp;

Query For Child table :- Select Id, EmpId, Salary from Emp_Salary;

O/P from both tables:-

Emp			Emp_Salary
Id	Name		Id	EmpId	Salary
1	Jon		101	1	100.01