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