FOREIGN KEY WITH DELETE CASCADE

DELETE CASCADE: Foreign key delete cascade means when parent table records is deleted the corresponding child table records also deleted.

--CREATE DEMOS DATABASE

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