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
- 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