定义SQL Server层叠引用完整性约束

层叠引用完整性约束是一种外键约束，用来告诉SQL Server在主键－外键关系被更新或者删除的时候采取特定的操作。通过使用层叠引用完整性约束，你可以定义在试图删除或者更新现有外键点的键时SQL Server应该采取的操作。

SQL Server可以让给你定义层叠引用完整性约束。这些操作有滴漏或者层叠效应，有的时候会影响到原来与主键表格相关的多个表格。现在就让我们来看看如何定义这些限制，以及你可以在哪些情况下使用它们。

下面的脚本会创建一些表格，我在介绍层叠引用完整性限制的时候将用到它们：

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

更新层叠或者删除语句的时候需要知道表格之间的关系；这些关系都通过FOREIGN KEY约束来定义。（点击这里获得更多关于定义SQL Server约束的信息。）下面的代码定义了SalesHistory和Customers表格之间的关系，以及SalesHistory和Products表格的关系。

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

前一个脚本介绍了层叠引用完整性选项。在第一个语句里，我使用ON UPDATE SET NULL选项；在第二个语句里，我使用了ON DELETE CASCADE ON UPDATE CASCADE选项。下面我简要介绍一些这些约束的功能。

SET NULL
如果一个删除语句影响到外键表格里的行，那么当主键记录被删除的时候，这些值可以被设置成NULL。如果一个更新语句影响到外键表格里的行，那么当主键记录被更新之后，这些行将被用NULL值更新。受影响的外键行必须允许接受NULL值。

CASCADE
如果一个删除语句影响到一个外键表格里的一个或者多个行，那么当主键记录被删除的时候，这些行就会被删除。如果更新语句影响到外键表格里的行，那么当主键记录被更新之后，这些行就被用来自主键记录的值更新。

SET DEFAULT
组成被引用行的外键的值被设置为默认的值。相关表格里所有的外键都必须有针对它们的默认的约束。

NO ACTION
这是默认的操作。这用来定义如果更新或者删除语句影响到外键表格里的值，那么操作会被拒绝或者回滚。系统会显示一条错误信息。

为了看看这些约束是如何起作用的，我会向表格里加入一些数据。

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

我可以把示例数据加载到SalesHistory表格里。由于我使用数值来表示客户，因此我可以通过系数运算符相对容易地生成CustomerID数。

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

现在让我们看看我设置的几个约束是否有效。下面的脚本会从Customers表格里删除一条客户记录。由于层叠约束指示ON DELETE SET NULL，SalesHistory表格里相关联的记录都会把CustomerID列的值设为NULL，而在更新之前，这个列的值是6。

DELETE FROM Customers
WHERE CustomerID = 6

这段脚本调用了在SalesHistory表格里定义的ON DELETE CASCADE。这也就是说，当一条记录从Products表格里被删除的时候，如果它与SalesHistory表格里的某条记录相关，那么这个SalesHistory记录也会被从表格里删除。

DELETE FROM Products
WHERE ProductID = 1

小心层叠

你可以在SQL Server里使用层叠约束把相关的值设置为NULL、把受影响的值设置为默认的值，或者删除列。虽然在测试或者质量保证环境（即数据不是生产数据，且可以重复使用）里使用这种能力非常理想，但是我不建议在生产环境里使用这种类型的约束。这样做的原因是，这些约束可能会造成你或者你的小组成员无法预知的问题，因此它们的代码可能无法控制层叠约束的操作。

如果你碰到需要从不同表格里删除相关数据的情况，我的经验是用定义好的过程来实现，而不要靠数据库来帮你完成。

Tim Chapman是肯塔基州路易维尔市一家银行的SQL Server数据库管理员，他有超过7年的行业经验。他还通过了微软SQL Server 2000和SQL Server 2005的认证。

责任编辑：德东

查看本文的国际来源