禁用和启用约束

禁用和启用约束

 

原文：http://msdn.microsoft.com/zh-CN/site/5dbc39e9-18fc-4f30-a98e-d0b9ec61e560

 

约束是在数据库中维护数据一致性的重要工具。但是，有时禁用一个或多个约束来执行某些任务（例如，使用脱机数据库进行数据同步）会更方便。在使用 SQL Server™ 复制技术同步数据库之间的数据时，可以告知单个对象在复制过程中不强制约束。

例如，在使用 NOT FOR REPLICATION 语句定义外键约束时，SQL Server 在复制过程中将不会强制约束。实际上，NOT FOR REPLICATION 语句可以直接用于定义外键约束、检查约束、标识和触发器的 T-SQL 语句中。对于使用 SQL Server 复制的操作，在适当的对象上使用 NOT FOR REPLICATION 语句是一种不错的选择。但是，如果您要手动执行数据同步，则另一个方法是手动禁用约束和触发器。

通常在需要同步数据子集以及需要更好地控制数据同步方法的联机/脱机应用程序中执行手动同步。在本月的专栏中，我将讨论何时手动禁用和启用约束会更有利，此方法可以帮助您解决哪些类型的问题以及一些解决问题的技巧。

禁用外键

我不建议从关系数据库中删除外键约束。但是，有时（例如在一系列表上执行大量的插入和更新操作以及需要更准确的结果和更佳的性能）您可能需要临时减少对一个或多个外键的引用完整性检查。当然，您只能在以正常的关系顺序无法对整个数据库执行大批量的数据更新时使用此方法。

因此何时才应禁用外键约束呢？假设您的关系数据结构有许多表，所有表都通过外键约束以某种方式彼此相关。与此数据库交互的应用程序具有一个脱机/移动版本，该版本与可能驻留在便携式计算机上的数据库的第二个实例进行通信。对主数据库所做的数据更改可能需要与脱机/移动数据库同步，而同步数据的方法有好几种。

同步数据的一个方法是将插入、更新和删除操作应用于脱机/移动数据库，以便与关系结构相一致。例如，在客户相应的订单前面插入客户，并在相应的订单详细信息前面插入订单。记录删除将按相反的方向进行（从子记录到父记录）。但是，在大型数据库结构上应用此方法时，可能因为太复杂而无法实现和维护。

另一个方法是删除外键约束，之后同步数据，然后重新创建外键约束。此方法只需进行比较小的改动（只需禁用外键约束）即可正常运行。禁用外键约束之后，可以同步数据，然后可以再次启用外键。禁用外键的语法如下所示：

1. -- Disable foreign key constraint
2. ALTER TABLE Orders
3. NOCHECK CONSTRAINT
4. FK_Orders_Customers
5. -- Add a new Order record for a non-existent customer
6. INSERT INTO Orders (CustomerID) VALUES ('BLAH')
7. -- Select all orders for the non-existent customer
8. SELECT * FROM Orders WHERE CustomerID = 'BLAH'

此外键强制“订单”表中的 CustomerID 必须是“客户”表中的有效 CustomerID。代码会禁用外键然后将订单插入到“订单”表。插入的订单记录的 CustomerID 在父“客户”表中不存在。由于外键已禁用，完整性检查会被忽略，订单记录将成功插入。

以下代码显示了重新启用外键约束然后测试外键约束工作是否正常的方法。执行此代码时，由于强制约束，订单并未被插入。系统将返回错误消息，表明插入语句与外键约束出现冲突。

1. -- Enable foreign key constraint
2. ALTER TABLE Orders
3. CHECK CONSTRAINT
4. FK_Orders_Customers
5. -- Add a new Order record for a non-existent customer
6. INSERT INTO Orders (CustomerID) VALUES ('BLEH')

在禁用外键、触发器和其他约束时，一定要确保在该时间段内不会在数据库上执行任何数据操作语言 (DML) 操作。这必须在手动执行禁用选项时进行处理。如果您使用 SQL Server 复制和 NOT FOR REPLICATION 语句同步数据，将会自动处理该条件。

禁用触发器

也可以在需要的时候禁用触发器。有时您并不想在同步数据时激发触发器。例如，如果您更新脱机/移动数据库（如上例所示）时，您可能没有注意到几个表中可能有触发器。在将行插入触发器所在的表从而导致该行被再次插入另一个表时，可能会激发这些触发器。但是在大型数据同步中，您可能并不希望再次插入这些类型的插入。为了避免出现这种不确定的事情，您可以临时禁用触发器，如下所示：

1. DISABLE TRIGGER MyTriggerName ON MyTableName

与之相反，若要启用触发器，请执行以下代码：

1. ENABLE TRIGGER MyTriggerName ON MyTableName

以下代码显示了当在“区域”表中插入或更新一行或多行时会激发的触发器（并显示一条信息）。

1. CREATE TRIGGER trRegion_InsertUpdate ON Region
2. FOR INSERT, UPDATE
3. AS
4. PRINT ' Trigger is running. '
5. + CAST(@@ROWCOUNT AS VARCHAR(10))
6. + ' row(s) affected.'
7. GO

您可以通过将一个新区域插入到“区域”表中测试此触发器，如下所示：

1. INSERT INTO Region (RegionID, RegionDescription)
2. VALUES (5, 'Some New Region')

在查询窗口中执行此代码时，新区域将被插入“区域”表中，然后触发器会被激发，消息窗格中将显示一条消息。若要禁用触发器，您可以执行以下代码：

1. DISABLE TRIGGER trRegion_InsertUpdate ON Region

那么，如果您执行此代码来更新新区域，则数据会被更新而不会激发触发器：

1. UPDATE Region SET RegionDescription = 'A Great Region'
2. WHERE RegionID = 5

若要重新启用触发器，只需执行以下查询：

1. ENABLE TRIGGER trRegion_InsertUpdate ON Region

同步脱机/移动数据库

禁用单个触发器或外键会很有用，但是在我介绍的脱机/移动数据库同步方案中，一次禁用所有触发器或外键将非常有用。若要执行此操作，您应执行以下步骤：禁用所有外键约束，禁用所有触发器，执行插入、更新和删除操作，启用所有外键约束，并最终重新启用所有触发器。

通过禁用触发器和外键约束，已经修改了数据的订单会变得不一致。在小型数据库（例如，罗斯文数据库）中，这种节省可能微不足道；但是在具有数十个或数百个表和关系的大型数据库中，将会节省大量时间。此外，如果没有此方法，则每次数据库架构添加新表和新关系时，您将不得不在脚本中修改表的顺序以便插入/更新/删除数据。禁用外键约束和触发器之后，由于顺序不再重要，因此您只需将表添加到脚本末尾即可。

请注意最后启用触发器和外键很重要，而不管脚本是否成功。例如，如果您的脚本禁用约束和触发器，并尝试修改数据，但未成功；您仍希望最后启用约束和触发器。

光标和信息架构视图

若要禁用所有外键，必须首先收集外键以及每个外键适用的表的列表。这里，您可以借助于光标和 INFORMATION_SCHEMA 视图。名为 INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS 的视图将返回当前数据库中的外键列表。若要获得外键适用的表的名称，请使用名为 INFORMATION_SCHEMA.TABLE_CONSTRAINTS 的视图。通过将以下代码加入这两个视图可以返回所有外键及其相应表的列表。

1. SELECT
2. ref.constraint_name AS FK_Name,
3. fk.table_name AS FK_Table
4. FROM
5. INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS ref
6. INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS fk
7. ON ref.constraint_name = fk.constraint_name
8. ORDER BY
9. fk.table_name,
10. ref.constraint_name

禁用所有外键

有了这个列表后，您可以编写脚本来禁用外键。您可以声明并打开光标，循环访问该列表，然后创建用于禁用外键的 T-SQL 命令，并针对每个外键执行该命令（请参见图 1）。

Figure 1 禁用和启用所有外键

1. CREATE PROCEDURE pr_Disable_Triggers_v2
2. @disable BIT = 1
3. AS
4. DECLARE
5. @sql VARCHAR(500),
6. @tableName VARCHAR(128),
7. @tableSchema VARCHAR(128)
8. -- List of all tables
9. DECLARE triggerCursor CURSOR
10. FOR
11. SELECT
12. t.TABLE_NAME AS TableName,
13. t.TABLE_SCHEMA AS TableSchema
14. FROM
15. INFORMATION_SCHEMA.TABLES t
16. ORDER BY
17. t.TABLE_NAME,
18. t.TABLE_SCHEMA
19. OPEN triggerCursor
20. FETCH NEXT FROM triggerCursor
21. INTO @tableName, @tableSchema
22. WHILE ( @@FETCH_STATUS = 0 )
23. BEGIN
24. IF @disable = 1
25. SET @sql = ‘ALTER TABLE ‘ + @tableSchema
26. + ‘.[‘ + @tableName + ‘] DISABLE TRIGGER ALL’
27. ELSE
28. SET @sql = ‘ALTER TABLE ‘ + @tableSchema
29. + ‘.[‘ + @tableName + ‘] ENABLE TRIGGER ALL’
30. PRINT ‘Executing Statement - ‘ + @sql
31. EXECUTE ( @sql )
32. FETCH NEXT FROM triggerCursor
33. INTO @tableName, @tableSchema
34. END
35. CLOSE triggerCursor
36. DEALLOCATE triggerCursor

首先，foreignKeyCursor 光标被声明为收集外键及其表名称列表的 SELECT 语句。接下来，打开光标并执行初始 FETCH 语句。此 FETCH 语句将第一行的数据读取到局部变量 @foreignKeyName 和 @tableName。

在循环光标时，您可以看到 @@FETCH_STATUS 的值为 0，这表示提取成功。这意味着循环将会继续，因此可以从行集中获取各个后续外键。

连接上的所有光标都可以使用 @@FETCH_STATUS。因此，如果您正在循环多个光标，一定要检查紧跟着 FETCH 语句的语句中 @@FETCH_STATUS 的值。@@FETCH_STATUS 将反映连接中最近的 FETCH 操作的状态。@@FETCH_STATUS 的有效值包括：

• 0 = FETCH 成功
• -1 = FETCH 失败
• -2 = 提取的行丢失

在循环内，代码会根据不同意图（禁用还是启用外键约束）来构建不同的 ALTER TABLE 命令（使用 CHECK 或 NOCHECK 关键字）。接下来，语句将被打印成一条消息（这样可以看到其进度），然后执行该语句。最后在循环处理所有行之后，存储过程关闭并释放光标。

禁用所有触发器

图 1 中的存储过程将禁用或启用数据库中的所有外键。在某些情况下，您可能希望在数据同步过程中禁用所有触发器。图 2 中的 pr_Disable_Triggers 存储过程正好可以完成该操作。

Figure 2 禁用和启用所有触发器

1. CREATE PROCEDURE pr_Disable_Triggers
2. @disable BIT = 1
3. AS
4. DECLARE
5. @sql VARCHAR(500),
6. @tableName VARCHAR(128),
7. @triggerName VARCHAR(128),
8. @tableSchema VARCHAR(128)
9. -- List of all triggers and tables that exist on them
10. DECLARE triggerCursor CURSOR
11. FOR
12. SELECT
13. so_tr.name AS TriggerName,
14. so_tbl.name AS TableName,
15. t.TABLE_SCHEMA AS TableSchema
16. FROM
17. sysobjects so_tr
18. INNER JOIN sysobjects so_tbl ON so_tr.parent_obj = so_tbl.id
19. INNER JOIN INFORMATION_SCHEMA.TABLES t
20. ON
21. t.TABLE_NAME = so_tbl.name
22. WHERE
23. so_tr.type = ‘TR’
24. ORDER BY
25. so_tbl.name ASC,
26. so_tr.name ASC
27. OPEN triggerCursor
28. FETCH NEXT FROM triggerCursor
29. INTO @triggerName, @tableName, @tableSchema
30. WHILE ( @@FETCH_STATUS = 0 )
31. BEGIN
32. IF @disable = 1
33. SET @sql = ‘DISABLE TRIGGER [‘
34. + @triggerName + ‘] ON ‘
35. + @tableSchema + ‘.[‘ + @tableName + ‘]’
36. ELSE
37. SET @sql = ‘ENABLE TRIGGER [‘
38. + @triggerName + ‘] ON ‘
39. + @tableSchema + ‘.[‘ + @tableName + ‘]’
40. PRINT ‘Executing Statement - ‘ + @sql
41. EXECUTE ( @sql )
42. FETCH NEXT FROM triggerCursor
43. INTO @triggerName, @tableName, @tableSchema
44. END
45. CLOSE triggerCursor
46. DEALLOCATE triggerCursor

pr_Disable_Triggers 存储过程将获取一个包含当前数据库中所有触发器及其相应表名称（和架构）的行集。由于没有 INFORMATION_SCHEMA.TRIGGERS 视图，我将从 SQL Server 2005 系统表和 INFORMATION_SCHEMA 视图中收集触发器列表和相关信息（如果可能）。

sysobjects 系统表位于所有数据库中，可以通过查询来返回所有触发器或表的列表。此系统表本身也可以加入以获取要操作的触发器和表列表，如图 2 中的光标声明所示。

如果在数据库上使用诸如 AdventureWorks 的存储过程（表在特定的架构中），您必须将架构名称作为表名称的前缀。pr_Disable_Triggers 存储过程通过加入 INFORMATION_SCHEMA.TABLES 视图来处理此情况，该视图返回表的 SCHEMA_NAME。

编写要禁用和重新启用触发器和外键的例程后，您可以在脚本中使用这些例程（该脚本用于修改脱机/移动数据库以使其与主数据库保持同步）。例如，您可以使用以下脚本（将中间的注释替换为数据操作运算）。

1. pr_Disable_Foreign_Keys 1
2. pr_Disable_Triggers 1
3. -- Perform data operations
4. pr_Disable_Foreign_Keys 0
5. pr_Disable_Triggers 0

迅速禁用所有触发器

您可以修改图 2 中显示的 pr_Disable_Triggers 存储过程来执行与前面稍有不同的 T-SQL 命令（该命令将启用或禁用所有触发器）。其中的 T-SQL 语句将使用 ALTER TABLE 命令（禁用或启用一个表中的所有触发器）的风格。通过使用该方法，您可以修改 pr_Disable_Triggers 存储过程，针对每个表执行以下语句来禁用该表上的所有触发器：

1. ALTER TABLE MySchemaName.MyTableName DISABLE TRIGGER ALL

通过使用该语法，光标的查询将收集全部表名称，而无需收集触发器本身的名称。因此，可以通过修改存储过程来使用该方法遍历表列表并启用或禁用每个表上的所有触发器。修改后的存储过程如图 3 所示。

Figure 3 使用 ALTER TABLE 命令禁用所有触发器

1. CREATE PROCEDURE pr_Disable_Foreign_Keys
2. @disable BIT = 1
3. AS
4. DECLARE
5. @sql VARCHAR(500),
6. @tableName VARCHAR(128),
7. @foreignKeyName VARCHAR(128)
8. -- A list of all foreign keys and table names
9. DECLARE foreignKeyCursor CURSOR
10. FOR SELECT
11. ref.constraint_name AS FK_Name,
12. fk.table_name AS FK_Table
13. FROM
14. INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS ref
15. INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS fk
16. ON ref.constraint_name = fk.constraint_name
17. ORDER BY
18. fk.table_name,
19. ref.constraint_name
20. OPEN foreignKeyCursor
21. FETCH NEXT FROM foreignKeyCursor
22. INTO @foreignKeyName, @tableName
23. WHILE ( @@FETCH_STATUS = 0 )
24. BEGIN
25. IF @disable = 1
26. SET @sql = ‘ALTER TABLE [‘
27. + @tableName + ‘] NOCHECK CONSTRAINT [‘
28. + @foreignKeyName + ‘]’
29. ELSE
30. SET @sql = ‘ALTER TABLE [‘
31. + @tableName + ‘] CHECK CONSTRAINT [‘
32. + @foreignKeyName + ‘]’
33. PRINT ‘Executing Statement - ‘ + @sql
34. EXECUTE(@sql)
35. FETCH NEXT FROM foreignKeyCursor
36. INTO @foreignKeyName, @tableName
37. END
38. CLOSE foreignKeyCursor
39. DEALLOCATE foreignKeyCursor

结束语

与从 .NET 应用程序逐一执行各个数据库操作相比，从 T-SQL 脚本执行多个数据库操作（如本文所述）会更高效。例如，您可以使用 ADO.NET 收集 .NET 应用程序的外键列表，然后执行相应的命令来禁用各个外键。同样可以将其扩展为获取触发器列表，然后禁用触发器。所有这些操作都需要在 .NET 代码和数据库之间来回进行操作，这与完全在 T-SQL 的数据库服务器上执行代码相比会占用更多的资源。

将您想向 John 询问的问题和提出的意见发送至：mmdata@microsoft.com mmdata@microsoft.com.

John Papa 是 ASPSOFT (aspsoft.com) 的高级 .NET 顾问，也是一名棒球迷，在夏季的夜晚，他的大多数时光都是和家人及其忠实的狗 Kadi 一起为洋基队加油助威度过的。John 是 C# 领域的一位 MVP，他撰写了多本有关 ADO、XML 和 SQL Server 的书籍，而且经常在行业会议（如 VSLive）上发表演讲，或者在 codebetter.com/blogs/john.papa 上发表博客文章。