Data Management

Defining cascading referential integrity constraints in SQL Server

Cascading referential integrity constraints are foreign key constraints that tell SQL Server to perform certain actions when a primary key field in a primary key-foreign key relationship is updated or deleted. By using cascading referential integrity constraints, you can define the actions that SQL Server 2005 takes when a user tries to delete or update a key to which existing foreign keys point.

Cascading referential integrity constraints are foreign key constraints that tell SQL Server to perform certain actions when a primary key field in a primary key-foreign key relationship is updated or deleted. By using cascading referential integrity constraints, you can define the actions that SQL Server 2005 takes when a user tries to delete or update a key to which existing foreign keys point.

SQL Server allows you to define cascading referential integrity constraints. These actions have a trickle-down or cascading effect, sometimes affecting several tables that were related to the primary key table. Let's look at how these constraints are defined, and some situations where you can use them.

The following script sets up the tables that I will use to look at cascading referential constraints:

IF OBJECT_ID('SalesHistory') > 0

DROP TABLE SalesHistory

GO

CREATE TABLE SalesHistory

(

SaleID int IDENTITY(1,1) NOT NULL,

ProductID TINYINT,

CustomerID INT,

SaleDate datetime NULL,

SalePrice money NULL,

CONSTRAINT pk_SaleID PRIMARY KEY (SaleID)

)

GO

IF OBJECT_ID('Customers') > 0

DROP TABLE Customers

GO

CREATE TABLE Customers

(

CustomerID INT,

FirstName VARCHAR(50),

LastName VARCHAR(50),

CONSTRAINT pk_CustomerID PRIMARY KEY (CustomerID)

)

GO

IF OBJECT_ID('Products') > 0

DROP TABLE Products

GO

CREATE TABLE Products

(

ProductID TINYINT,

ProductDescription VARCHAR(100),

CONSTRAINT pk_ProductID PRIMARY KEY (ProductID)

)

GO

Relationships between tables are required for cascading updates or delete statements; these relationships are defined through FOREIGN KEY constraints. (Here's more information on defining SQL Server constraints.) The code below defines a relationship between the SalesHistory and the Customers tables and a relationship between the SalesHistory and the Products tables.

ALTER TABLE SalesHistory

ADD CONSTRAINT fk_SalesHistoryCustomerID FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) ON DELETE SET NULL ON UPDATE SET NULL

ALTER TABLE SalesHistory

ADD CONSTRAINT fk_SalesHistoryProductID FOREIGN KEY (ProductID) REFERENCES Products(ProductID) ON DELETE CASCADE ON UPDATE CASCASE

GO

The previous script introduces the cascading referential integrity options. In the first statement, I use the ON UPDATE SET NULL option; in the second statement, I use the ON DELETE CASCADE ON UPDATE CASCADE option. Here's an overview of what these constraints mean.

SET NULL

If a delete statement affects rows in a foreign key table, those values will be set to NULL when the primary key record is deleted. If an update statement affects rows in the foreign key table, those rows will be updated with the value NULL after the primary key record has been updated. The foreign key columns affected must allow NULL values.

CASCADE

If a delete statement affects one or more rows in a foreign key table, those rows will be deleted when the primary key record is deleted. If an update statement affects rows in the foreign key table, those rows will be updated with the value from the primary key record after it has been updated.

SET DEFAULT

All the values that make up the foreign key in the rows that are referenced are set to their default value. All foreign key columns in the related table must have default constraints defined on them.

NO ACTION

This is the default action. This specifies that if an update or delete statement affects rows in foreign key tables, the action will be denied and rolled back. An error message will be raised.

To see how these constraints work, I'll add some data to my tables.

INSERT INTO Products

(

ProductID, ProductDescription

)

SELECT 1, 'BigScreen'

UNION ALL

SELECT 2, 'Computer'

UNION ALL

SELECT 3, 'PoolTable'

GOINSERT INTO Customers(CustomerID , FirstName , LastName )

SELECT 1, 'Jason', 'Tomes'

UNION ALL

SELECT 2, 'Chris', 'Robards'

UNION ALL

SELECT 3, 'Megan', 'Hill'

UNION ALL

SELECT 4, 'Wanda', 'Guthrie'

UNION ALL

SELECT 5, 'Lilly', 'Cunningham'

UNION ALL

SELECT 6, 'Amanda', 'Travis'

UNION ALL

SELECT 7, 'Willy', 'Grant'

UNION ALL

SELECT 8, 'Zach', 'Tacoma'

UNION ALL

SELECT 9, 'Marty', 'Smith'

UNION ALL

SELECT 10, 'Wendi', 'Jones'

UNION ALL

SELECT 11, 'Angie' , 'Corolla'

UNION ALL

SELECT 12, 'Shelly', 'Hartson'

GO

I can load sample data into the SalesHistory table. Because I am using a numeric value to represent the customers, I can generate the CustomerID number with relative ease with the use of the modulus operator.

DECLARE @i SMALLINT

SET @i = 1

WHILE (@i <=100)

BEGIN

INSERT INTO SalesHistory(ProductID, SaleDate, SalePrice, CustomerID)

VALUES(1, DATEADD(mm, @i, '3/11/1919'), DATEPART(ms, GETDATE()) + (@i + 57), ((DATEPART(ms, GETDATE()) + (@i + 29)) % 12) + 1)

INSERT INTO SalesHistory(ProductID, SaleDate, SalePrice, CustomerID)

VALUES(2, DATEADD(mm, @i, '3/11/1927'), DATEPART(ms, GETDATE()) + (@i + 13), ((DATEPART(ms, GETDATE()) + (@i + 29)) % 12) + 1)

INSERT INTO SalesHistory(ProductID, SaleDate, SalePrice, CustomerID)

VALUES(3, DATEADD(mm, @i, '3/11/1908'), DATEPART(ms, GETDATE()) + (@i + 29), ((DATEPART(ms, GETDATE()) + (@i + 29)) % 12) + 1)

SET @i = @i + 1

END

Now let's see if the constraints I set up work. The script below deletes a customer record from my Customers table. Because the cascading constraint indicates ON DELETE SET NULL, the associated records in the SalesHistory table will have the value NULL for the CustomerID column where that column had the value of 6 before the update.

DELETE FROM Customers

WHERE CustomerID = 6

This script invokes the ON DELETE CASCADE constraint defined on the SalesHistory table. This means that when a record is deleted from the Products table, and it is related to a record in the SalesHistory table, those SalesHistory records will be deleted from the table.

DELETE FROM Products

WHERE ProductID = 1

Cascade with care

You can use cascading constraints in SQL Server to set related values to NULL, to set affected values to original default values, or to delete columns. While it's great to have these capabilities in a testing or quality assurance environment (where data is not production data and consistently reused), I don't recommend allowing these types of constraints in a production environment. The reason for this is that these constraints may lead to results that you or other programmers on your team are not aware of, and so their code may not be able to handle the actions from the cascading constraint.

If you are in a situation where you need to delete related data from different tables, it has been my experience to have defined procedures in place to do so, rather than to rely on the database to do this for you.

Tim Chapman is a SQL Server database administrator who works for a bank in Louisville, KY, and has more than 7 years of IT experience. He is also Microsoft certified in SQL Server 2000 and SQL Server 2005. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.

-----------------------------------------------------------------------------------------

Get SQL tips in your inbox

TechRepublic's free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!

About

Tim Chapman is a SQL Server MVP, a database architect, and an administrator who works as an independent consultant in Raleigh, NC, and has more than nine years of IT experience.

8 comments
gspiv
gspiv

The problem we are having with cascade deletes is in the case where a table has foreign keys to two different parent tables, which in turn have a common ancestor. Since SQL Server does not allow both FKs to have a cascade, we put a cascade on only one. When we delete the common ancestor the result is inconsistent. Sometimes SQL is able to do it, and sometimes it tries to delete first from the parent without the cascade, and fails with a reference error. This seems to be unintelligent behavior on the part of SQL Server. Has anyone found a solution for this? Tim's suggestion to use stored procedures is a lot of trouble, but it's the only foolproof solution I've heard.

eeidfn
eeidfn

Hi all, Wait a minute!!! Cascade delete is a powerful feature that can be made available to the user through an "YES" / "NO" warning dialog box. If the user selects YES, the selected entity and all associated child/grandchild entities will be deleted, that is his/her choice. But if NO is selected, the user can delete those specific grandchild and child entities first before attempting to delete the parent entity. Cascade Delete is like deleting a folder that contains a hierarchy (with sub-folders and files) under it, isn't it ? Windows allows deletion of a given folder and the entire sub-tree. Should not database entities be handled the same way, if the relationships between tables are hierarchical in nature ? /eeidfn

sirswaom
sirswaom

what is equivelenof oracle statement drop table abc cascade constraints in sqlserver 2005 or more

mattohare
mattohare

I'll get into a car on a down hill slope to the Grand Canyon with dodgy breaks. I should think it would be pretty easy to write procedures to handle child records. This sort of 'automatic override' strategy seemed to be the cause of many of the bugs I found in older Access/JET systems. First and foremost is data integrity. That is a very specific need to the people that write the checks.

chapman.tim
chapman.tim

Apparently you didn't read the last section of the article.

mattohare
mattohare

And you're right, my reply didn't really acknowledge your comments at the end. It's just that I've run into a lot of [fairly novice] developers that would not do more than scan your last section either, and then write something for members of their work group. Will I ever forget the 'industrial' application, sold like a product on a store shelf, that would let you delete a major entity, and wipe out a major part of the data store. This was a student registration system. They'd delete instructors and wonder why the students' credentials were gone. They'd delete some students and wonder why it seemed an instructor was under used. This was an old application written off JET 2.0. I was charged with, among other things, writing a backup facility for the thing. I was a bit over critical on that. I'm sorry for that. Bad netiquette by me. I'll put my hands on the cooker later, will I? *chuckle* I do hope my reply does highlight the cliff edge with widely used applications.

rwilson
rwilson

and only scan the middle portion if you must! ;-) Unfortunately, in order to caution someone about Cascading Actions (or fire arms) one must introduce them to those tools. However, I have occassionally found the NO ACTION option to be quite helpful. Having the NO ACTION option lets you prevent data from becoming an Orphan unless one consciously makes an enormous effort to do so. In the case of the deletion of the data from the Professors table, would it not have been better to retain at least a portion of the information about the professor (e.g. name, hire date, termination date) so that Classes table could still have a valid reference as well as the StudentCredits and so that at least some sort of minimal validity checking could be made on claims of someone having taken a course under a terminated professor? If one needs to delete a higher level entity that has children (and grandchildren?), having the NO ACTION option forces the deletion to start at the lowest point and work its way up.