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

  1. CREATE DATABASE DEMOS  
  2. USE DEMOS  
  3.   
  4. CREATE TABLE DBO.DEPT  
  5. (  
  6. ID INT PRIMARY KEY,  
  7. DEPTNAME VARCHAR(100)  
  8. )  
Insert data to DEPT Table
  1. INSERT INTO DBO.DEPT(ID,DEPTNAME) VALUES(1,'SOFTWARE'),(2,'BPO')  
Create EMP Table
  1. CREATE TABLE DBO.EMP  
  2. (  
  3. ID INT IDENTITY(1,1) PRIMARY KEY,  
  4. FIRTSNAME VARCHAR(100) ,  
  5. LASTNAME VARCHAR(100),  
  6. LOCATION VARCHAR(100),  
  7. DOB DATETIME,  
  8. SALARY MONEY,  
  9. DEPT INT   
  10. )  
Add FOREIGN Key to EMP Table
  1. ALTER TABLE DBO.EMP  
  2. ADD CONSTRAINT FK_DEPTID FOREIGN KEY (DEPT)   
  3. REFERENCES DBO.DEPT(ID) ON DELETE CASCADE ON UPDATE CASCADE  
Insert some data to EMP Table
  1. INSERT INTO DBO.EMP(FIRTSNAME,LASTNAME,LOCATION,DOB,SALARY,DEPT)  
  2. VALUES  
  3. ('RAKESH','KALLURI','HYDERABAD','07-23-1989',24000,1),  
  4. ('NARESH','CH','PUNE','07-23-1987',48000,1),  
  5. ('SRUJAN','KUMAR','HYDERABAD','07-23-1988',25000,1),  
  6. ('VENKATESH','BODUPPALY','HYDERABAD','07-23-1986',32000,2),  
  7. ('ALI','MD','HYDERABAD','07-23-1987',38000,2),  
  8. ('GANGA','RAJAYAM','PUNE','05-26-1987',390000,2),  
  9. ('RAVI','KUMAR','CHENNAI','03-23-1986',47000,1),  
  10. ('PRAVEEN','KUMAR','DELHI','07-23-1988',33000,2)  
  11.   
  12. SELECT * FROM DBO.DEPT  
DEPT
  1. SELECT * FROM DBO.EMP  
DBO
  1. 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.

Delete records
Check the records after DELETE
  1. SELECT * FROM DBO.EMP  
Check the records

 

Next Recommended Reading SQL Constraint - Foreign Key