ON UPDATE/DELETE CASCADE SQL SERVER

ON UPDATE/DELETE CASCADE


Let's explore CASCADE and see how it work.

USE Test2
GO

--Let's create a parent table Named Employee

IF EXISTS (SELECT * FROM sys.objects
WHERE name = N'Employees' AND [type] = 'U')
DROP TABLE Employees
CREATE TABLE Employees
(
EmplID INT  PRIMARY KEY ,
FName VARCHAR(30),
MName VARCHAR(1),
LName VARCHAR(30),
Division VARCHAR(30)
)
GO

--Let's create a child table Named Employee Education

IF Exists (SELECT * FROM sys.objects WHERE name = N'EmployeeEducation' AND [type] = 'U')
DROP TABLE EmployeeEducation;
CREATE TABLE EmployeeEducation (
EducationID int IDENTITY (100, 1) Primary Key,
EmplID int, 
HighSchoolName Varchar(70),
HS_City Varchar(50),
YearPassed int,
UnderGraduate Varchar(70),
UG_City Varchar(50),
UG_YearPassed int,
Graduated bit,
)
GO

--Create Foreign Key relationship between Employee and EmployeeEducation Table

IF EXISTS (SELECT * FROM sys.objects where name = N'FK_EmployeeEducation_Employees' and [type] = 'F')
ALTER TABLE EmployeeEducation
DROP CONSTRAINT FK_EmployeeEducation_Employees
GO

ALTER TABLE EmployeeEducation
ADD CONSTRAINT [FK_EmployeeEducation_Employees]
Foreign Key (EmplID) References [Employees](EmplID)

--Time to Populate Employee Table


Insert INTO Employees 
SELECT 1,'David', 'D', 'Moore', 'IT' UNION ALL
SELECT 2,'AtulKumar', 'G', 'Patel', 'MKT' UNION ALL
SELECT 3,'Sanjay', 'C', 'Kumar', 'HR' UNION ALL
SELECT 4,'Telly', 'M', 'Morris', 'Purchasing'

Select * FROM Employees

--Time to Populate EmployeeEducation Table

Insert INTO EmployeeEducation
SELECT 1,'Saint Thomas High School', 'Dallas',1987,' BS Computer Science', 'Richardson', 1992, 0 UNION ALL
SELECT 2,'Saint Patrick Monroe School', 'Richardson',1988,' BS Artificial Intelligence', 'Plano', 1990, 1 UNION ALL
SELECT 3,'Saint Thomas High School', 'Plano',1989,' BS Computer Science', 'Irving', 1994, 1 UNION ALL
SELECT 4,'Saint Thomas High School', 'Irving',1990,' BS Computer Science', 'Wichita', 1995, 0 

Select * FROM EmployeeEducation


--Now try to Update Employee Table For 'David'

UPDATE Employees
SET EmplID = 100
Where EmplID = 1

--Msg 547, Level 16, State 0, Line 1
--The DELETE statement conflicted with the REFERENCE constraint "FK_EmployeeEducation_Employees". The conflict occurred in database "PK_Test", table "dbo.EmployeeEducation", column 'EmplID'.
--The statement has been terminated.


--Now Try to Update Employee Table

DELETE FROM Employees
Where EmplID = 2

--We get this error message
--"Msg 547, Level 16, State 0, Line 2
--The DELETE statement conflicted with the REFERENCE constraint "FK_EmployeeEducation_Employees". The conflict occurred in database "PK_Test", table "dbo.EmployeeEducation", column 'EmplID'.
--The statement has been terminated."

--BRING CASCADE ON

--Let's first drop FK Relationship

IF EXISTS (SELECT * FROM sys.objects
WHERE name = N'FK_EmployeeEducation_Employees' AND [type] = 'F')
ALTER TABLE EmployeeEducation
DROP Constraint FK_EmployeeEducation_Employees
GO

--Lets Add CASCADE OPTION to Our Constraints

ALTER TABLE EmployeeEducation
ADD CONSTRAINT [FK_EmployeeEducation_Employees]
FOREIGN KEY (EmplID) References [Employees](EmplID)
ON DELETE CASCADE ON UPDATE CASCADE 
GO

--Now try this time to Update Employee Table For 'David'

Do a Select of both Table.

Select * FROM  Employees;
Select * FROM EmployeeEducation;


This is what you will get.

Now run this UPDATE


UPDATE Employees
SET EmplID = 5
Where EmplID = 1

And now this will be the result:

--So we see that with Cascade On, we were able to update or delete.RIGHT!!!!

--Question is: Why do we want to USE or NOT USE CASCADE ON Option

-- The real use of CASCADE ON is when we have primary key which are not IDENTITY Column in many real world situation. For example Lets say we have 10 digit SSN number sitting somewhere in our government database. we have a master table which maintain SSN and thousands of other tables (banks, benefits, etc) which is referencing this column. Lets say we make a request to change our SSN from government. They obliged to change our SSN number (Well thank if they are able to do it!!!). In that case, lets assume that various bank agency,HR, and thousands of other departments need to be updated at the sametime.

--So by using CASCADE option for UPDATE, if we update SSN in main table, automatically it will be updated every other table it is being referenced.

If you use a natural key (e.g. a regular field from your database table) as your primary key, then there might be certain situations where you need to update your primary keys. Another recent example would be the ISBN (International Standard Book Numbers) which changed from 10 to 13 digits+characters not too long ago.

Follow MSDN for reference: Cascade on MSDN