SQL Server 2008 R2 - Foreign Key - DELETE CASCADE and DELETE UDPATE

Following blog travers you through step by step information about creating FKs and ON DELETE Action.

--1. Create a table Employeeinfo
CREATE TABLE EMPLOYEEINFO
(
Empid INT IDENTITY(1,1) PRIMARY KEY
,Empname VARCHAR(20)
,DeptId  TINYINT
)

--2. Create another table DeptInfo
CREATE TABLE DeptInfo
(
DeptId TINYINT REFERENCES EMPLOYEEINFO(DeptId)
,Deptname VARCHAR(20)
)

You get following error while creating the FK because the DeptId in EmployeeInfo is neither PK nor unique. An FK doesn't have to be linked only to a PK in another table. It can also reference to a UNIQUE constraint in another table.

Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 'EMPLOYEEINFO' that match the referencing column list in the foreign key 'FK__DeptInfo__DeptId__08EA5793'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

--1. Create a table Employeeinfo
CREATE TABLE EMPLOYEEINFO
(
Empid INT IDENTITY(1,1) PRIMARY KEY
,Empname VARCHAR(20)
,DeptId  TINYINT UNIQUE
)
--2. Create another table DeptInfo
CREATE TABLE DeptInfo
(
DeptId TINYINT PRIMARY KEY
,Deptname VARCHAR(20)
)

ALTER TABLE DeptInfo
ADD CONSTRAINT FK_DeptInfo_DeptId FOREIGN KEY (DeptId)
REFERENCES EMPLOYEEINFO(DeptId)

SELECT *
FROM    sys.foreign_keys
-- There are two columns which define the delete and update action. The columns are delete_referential_action_desc and update_referential_action_desc.

0 - NO ACTION
1 - CASCADE
2 - SET NULL
3 - SET DEFAULT

Custom Search

--Inserting data in these tables

INSERT INTO DeptInfo VALUES(1,'HR')
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_DeptInfo_DeptId". The conflict occurred in database "TestDB", table "dbo.EMPLOYEEINFO", column 'DeptId'.
The statement has been terminated.

INSERT INTO EMPLOYEEINFO VALUES(1,'tuitionaffordable','HR')

Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'EMPLOYEEINFO' can only be specified when a column list is used and IDENTITY_INSERT is ON.

INSERT INTO EMPLOYEEINFO VALUES('tuitionaffordable',1)
INSERT INTO DeptInfo VALUES(1,'HR')

--Lets try to delete data from DeptInfo

DELETE Deptinfo -- Successful

But if you try to delete the information from Employeeinfo then you get error:

DELETE EMPLOYEEINFO
WHERE DeptId = 1

Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK_DeptInfo_DeptId". The conflict occurred in database "TestDB", table "dbo.DeptInfo", column 'DeptId'.
The statement has been terminated.

This error can be fixed with ON DELETE ACTION. There can be four types of actions "ON DELETE" and "ON UPDATE".

0 - NO ACTION
1 - CASCADE
2 - SET NULL
3 - SET DEFAULT

--1. Create a table Employeeinfo
CREATE TABLE EMPLOYEEINFO
(
Empid INT IDENTITY(1,1) PRIMARY KEY
,Empname VARCHAR(20)
,DeptId  TINYINT UNIQUE
)
--2. Create another table DeptInfo
CREATE TABLE DeptInfo
(
DeptId TINYINT PRIMARY KEY REFERENCES EMPLOYEEINFO(DeptId) ON DELETE CASCADE
,Deptname VARCHAR(20)
)

INSERT INTO EMPLOYEEINFO VALUES('tuitionaffordable',1)
INSERT INTO DeptInfo VALUES(1,'HR')

DELETE EMPLOYEEINFO
WHERE DeptId = 1 -- This will not throw any error. The DeptId 1 is also deleted from DeptInfo table.

Regards,
http://tuitionaffordable.webstarts.com