SQL Server – Foreign Key References – With Cascade

A foreign key is a mechanism for ensuring referential integrity in the SQL Server database. A foreign key indicates that values from one table must also appear in another.

The referenced table is referred to as the parent table, while the table containing the foreign key is referred to as the child table. This means the foreign key in the child table will correspond to a main key in the parent table.

Actions that would break linkages between tables are stopped by the FOREIGN KEY restriction. If there are dependency items in the child table, it is not possible to change or delete the parent table’s column entries. Cascading options, on the other hand, can be used to update or delete parent data without breaking the link between the tables.

Consider the below dataset which is with foreign key relationship without using the cascading options.

— Table definition – Dept
CREATE TABLE Dept(
DeptNo INT,
DName VARCHAR(14),
Loc VARCHAR(13),
CONSTRAINT pk_Dept PRIMARY KEY (DeptNo))

— Tabe Definition – Emp
CREATE TABLE Emp(
EmpNo INT,
EName VARCHAR(10),
Job VARCHAR(9),
Mgr INT,
HireDate date,
Sal INT,
Comm INT,
DeptNo INT,
CONSTRAINT pk_Emp PRIMARY KEY (EmpNo),
CONSTRAINT fk_DeptNo FOREIGN KEY (DeptNo) REFERENCES Dept (DeptNo));

— Dept Table data:
INSERT INTO Dept VALUES(10, ‘ACCOUNTING’, ‘NEW YORK’);
INSERT INTO Dept VALUES(20, ‘RESEARCH’, ‘DALLAS’);
INSERT INTO Dept VALUES(30, ‘SALES’, ‘CHICAGO’);
INSERT INTO Dept VALUES(40, ‘OPERATIONS’, ‘BOSTON’);

— Emp Table data:
INSERT INTO Emp VALUES( 7839, ‘KING’, ‘PRESIDENT’, NULL, CONVERT(DATETIME,’17-11-1981′,103), 5000, NULL, 10 );
INSERT INTO Emp VALUES( 7698, ‘BLAKE’, ‘MANAGER’, 7839, CONVERT(DATETIME,’1-5-1981′,103), 2850, NULL, 30 );
INSERT INTO Emp VALUES( 7782, ‘CLARK’, ‘MANAGER’, 7839, CONVERT(DATETIME,’9-6-1981′,103), 2450, NULL, 10 );
INSERT INTO Emp VALUES( 7566, ‘JONES’, ‘MANAGER’, 7839, CONVERT(DATETIME,’2-4-1981′,103), 2975, NULL, 20 );
INSERT INTO Emp VALUES( 7788, ‘SCOTT’, ‘ANALYST’, 7566, CONVERT(DATETIME,’13-JUL-87′,103), 3000, NULL, 20 );
INSERT INTO Emp VALUES( 7902, ‘FORD’, ‘ANALYST’, 7566, CONVERT(DATETIME,’3-12-1981′,103), 3000, NULL, 20 );
INSERT INTO Emp VALUES( 7369, ‘SMITH’, ‘CLERK’, 7902, CONVERT(DATETIME,’17-12-1980′,103), 800, NULL, 20 );
INSERT INTO Emp VALUES( 7499, ‘ALLEN’, ‘SALESMAN’, 7698, CONVERT(DATETIME,’20-2-1981′,103), 1600, 300, 30 );
INSERT INTO Emp VALUES( 7521, ‘WARD’, ‘SALESMAN’, 7698, CONVERT(DATETIME,’22-2-1981′,103), 1250, 500, 30 );
INSERT INTO Emp VALUES( 7654, ‘MARTIN’, ‘SALESMAN’, 7698, CONVERT(DATETIME,’28-9-1981′,103), 1250, 1400, 30 );
INSERT INTO Emp VALUES( 7844, ‘TURNER’, ‘SALESMAN’, 7698, CONVERT(DATETIME,’8-9-1981′,103), 1500, 0, 30 );
INSERT INTO Emp VALUES( 7876, ‘ADAMS’, ‘CLERK’, 7788, CONVERT(DATETIME,’13-JUL-87′, 103), 1100, NULL, 20 );
INSERT INTO Emp VALUES( 7900, ‘JAMES’, ‘CLERK’, 7698, CONVERT(DATETIME,’3-12-1981′,103), 950, NULL, 30 );
INSERT INTO Emp VALUES( 7934, ‘MILLER’, ‘CLERK’, 7782, CONVERT(DATETIME,’23-1-1982′,103), 1300, NULL, 10 );
Go

The result, after executing the above statements.

As you can see, the Dept entries have dependencies in the Emp table. Let’s examine what happens if we modify the parent entries.

UPDATE Dept SET DeptNo = 101 WHERE DeptNo = 10

The error we receive is as follows, and it is expected.

Msg 547, Level 16, State 0, Line 55
The UPDATE statement conflicted with the REFERENCE constraint “fk_DeptNo”. The conflict occurred in database “TestDatabase”, table “dbo.Emp”, column ‘DeptNo’.
The statement has been terminated.

Let’s try out the cascade options now.

Since we can’t add the cascade option to the existing constraint, let’s drop and recreate it using the following statements.

ALTER TABLE Emp DROP CONSTRAINT fk_DeptNo

ALTER TABLE Emp ADD CONSTRAINT fk_DeptNo FOREIGN KEY (DeptNo)
REFERENCES Dept(DeptNo) ON UPDATE CASCADE;

Now, let’s see what happens if we change the parent records.

UPDATE Dept SET DeptNo = 101 WHERE DeptNo = 10

The requested value was successfully updated in the parent record. However, as shown below, the child entries (in the Emp table) were also modified in order to avoid a break in the link between the parent and child records.

Now, let’s try with DELETE CASCADE.

ALTER TABLE Emp DROP CONSTRAINT fk_DeptNo;

ALTER TABLE Emp ADD CONSTRAINT fk_DeptNo FOREIGN KEY (DeptNo)
REFERENCES Dept(DeptNo) ON DELETE CASCADE;

Now, let’s see what happens if we delete the parent records.

DELETE FROM Dept WHERE DeptNo = 101;

The requested value was successfully deleted from the parent record. However, as shown below, the child entries (in the Emp table) were also deleted since they are of no use without their parent records.

Hope you find this article helpful.

Happy learning!!