Foreign Key with Delete CASCADE in SQL

DELETE CASCADE

Foreign key delete cascade means when parent table records is deleted, then the corresponding child table records also get deleted.

Create DEMOS Datatable

CREATE DATABASE DEMOS
USE DEMOS

CREATE TABLE DBO.DEPT
(
ID INT PRIMARY KEY,
DEPTNAME VARCHAR(100)
)

Insert data to DEPT Table

INSERT INTO DBO.DEPT(ID,DEPTNAME) VALUES(1,'SOFTWARE'),(2,'BPO')

Create EMP Table

CREATE TABLE DBO.EMP
(
ID INT IDENTITY(1,1) PRIMARY KEY,
FIRTSNAME VARCHAR(100) ,
LASTNAME VARCHAR(100),
LOCATION VARCHAR(100),
DOB DATETIME,
SALARY MONEY,
DEPT INT
)

Add FOREIGN Key to EMP Table

ALTER TABLE DBO.EMP
ADD CONSTRAINT FK_DEPTID FOREIGN KEY (DEPT)
REFERENCES DBO.DEPT(ID) ON DELETE CASCADE ON UPDATE CASCADE

Insert some data to EMP Table

INSERT INTO DBO.EMP(FIRTSNAME,LASTNAME,LOCATION,DOB,SALARY,DEPT)
VALUES
('RAKESH','KALLURI','HYDERABAD','07-23-1989',24000,1),
('NARESH','CH','PUNE','07-23-1987',48000,1),
('SRUJAN','KUMAR','HYDERABAD','07-23-1988',25000,1),
('VENKATESH','BODUPPALY','HYDERABAD','07-23-1986',32000,2),
('ALI','MD','HYDERABAD','07-23-1987',38000,2),
('GANGA','RAJAYAM','PUNE','05-26-1987',390000,2),
('RAVI','KUMAR','CHENNAI','03-23-1986',47000,1),
('PRAVEEN','KUMAR','DELHI','07-23-1988',33000,2)

SELECT * FROM DBO.DEPT

SELECT * FROM DBO.EMP

DELETE FROM DBO.DEPT WHERE ID=1

Whenever we are trying to delete records from DEPT table whose DEPTID is 1, this child EMP table records also get deleted.


Check the records after DELETE

SELECT * FROM DBO.EMP