Microsoft® SQL Server® 2012 Bible

312

Part II: Building Databases and Working with Data

Cascading Deletes

Referential integrity (RI) refers to the idea that no secondary row foreign key should point to

a primary row primary key unless that primary row exists. This means that an attempt to

delete a primary row can fail if a foreign-key value somewhere points to that primary row.

For more information about referential integrity and when to use it, turn to Chapter 7, “Relational

Database Design and Creating the Physical Database Schema,” and Chapter 2, “Data Architecture.”

When correctly implemented, referential integrity blocks any delete operation that would

result in a foreign key value without a corresponding primary key value. The way around

this is to fi rst delete the secondary rows that point to the primary row, and then delete the

primary row. This technique is called a cascading delete. In a complex database schema, the

cascade might bounce down several levels before working its way back up to the original

row being deleted.

You can implement a cascading delete in two ways: manually with triggers or automatically

with declared referential integrity (DRI) via foreign keys.

Manually implementing cascading deletes is a lot of work. Triggers are signifi cantly slower

than foreign keys (which are checked as part of the query execution plan), and trigger-

based cascading deletes usually also handle the foreign key checks. Although this was com-

monplace a decade ago, today trigger-based cascading deletes are rare and might be needed

only with a complex nonstandard foreign key design that includes business rules in the

foreign key. If you do that, then you’re either new at this or very, very good.

Fortunately, SQL Server offers cascading deletes as a function of the foreign key. Cascading

deletes may be enabled via Management Studio, in the Foreign Key Relationship dialog, or

in SQL code.

The following script drops an existing foreign key relationship between the

HumanResource.JobCandidate and the HumanResources.Employee tables in the

AdventureWorks database. Next it adds a new foreign key between the table setting

the cascade delete option for referential integrity. The ON DELETE CASCADE foreign-key

option is what actually specifi es the cascade action:

USE [AdventureWorks]

GO

ALTER TABLE [HumanResources].[JobCandidate]

DROP CONSTRAINT [FK_JobCandidate_Employee_BusinessEntityID]

GO

ALTER TABLE [HumanResources].[JobCandidate]

WITH CHECK ADD CONSTRAINT [FK_JobCandidate_Employee_BusinessEntityID]

FOREIGN KEY([BusinessEntityID])

c12.indd 312c12.indd 312 7/30/2012 4:42:41 PM7/30/2012 4:42:41 PM

http://www.it-ebooks.info