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!
