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.

Editor's Picks