SQL Server：使用外键删除表

Question

表"A"通过外键“A.Id”与其他约30个表相关联。

为了进行集成测试，我必须删除并重新创建该表以创建一个定义的状态。由于存在依赖对象，似乎没有办法删除和重新创建该表。错误信息如下：

无法删除对象“dbo.A”，因为它被FOREIGN KEY约束引用

问题：

• 我该如何删除并重新创建表"A"？
• （或者）是否有一种全局关闭架构依赖项的方法？
• （或者）是否有一种在删除和恢复表"A"之前备份（全部！）依赖项并在之后还原所有依赖项的方法？

- Robert

---

为什么不使用单独的数据库进行集成测试，每次都从头开始生成整个状态？ - user253984

数据库太大了（有几百个表和许多相关对象）。按照这种方式，每个集成测试启动时间就需要约45秒+x，而且没有任何实际测试。此外，一些表包含数据。（完整的数据库转储文件大小约为35MB，不包括任何自定义数据）。我们将有很多集成测试，希望在每次检查时使用集成服务器执行。 - Robert

每个整合状态是否需要相同的数据库初始状态，或者您只需支付启动惩罚一次，并使每个测试撤销其可能对状态所做的任何短暂更改？ - Mikeb

只需支付一次启动惩罚的想法。 "倒带"更改的想法是重新创建表格+定义的数据集。 （它是一个庞大的遗留应用程序，这是我们迄今为止能想出的最好的方法。目标是单元测试，但目前我们只能进行集成测试。） - Robert

Answer 1

探索 sys.foreign_key_columns 系统表。以下是一个例子，它将在给定一个表的情况下告诉你哪些列与另一个表相关联：

DECLARE @tableName VARCHAR(255)
SET @tableName = 'YourTableName'

SELECT OBJECT_NAME(fkc.constraint_object_id) AS 'FKName', OBJECT_NAME(fkc.[referenced_object_id]) AS 'FKTable', c2.[name] AS 'FKTableColumn', @tableName AS 'Table', c1.[name] AS 'TableColumn'
    FROM sys.foreign_key_columns as fkc
        JOIN sys.columns AS c1 ON c1.[object_id] = fkc.[parent_object_id] AND c1.[column_id] = fkc.[parent_column_id]
        JOIN sys.columns AS c2 ON c2.[object_id] = fkc.[referenced_object_id] AND c2.[column_id] = fkc.[referenced_column_id]
    WHERE fkc.[parent_object_id] = OBJECT_ID(@tableName)
    ORDER BY OBJECT_NAME(fkc.constraint_object_id)

通过这种方式或其变体，您可以找出外键，删除它们，进行操作，然后重新创建外键。

我应该补充说明，我知道这适用于SQL2005和SQL2008。我不确定它是否适用于SQL2000 / MSDE。

- Yoopergeek

Answer 2

在管理工作室中，您可以右键单击表并脚本创建和删除操作，其中将包括所有外键。

---

更具体地说，这将为您提供表所依赖的所有约束条件。然而，它不会给出依赖于该表的外键列表。因此，除了通过在SMS中右键单击表生成的脚本之外，您还需要查找和脚本化所有外键。要获取它们的列表，您可以运行以下查询：

select FKConstraint.TABLE_NAME, FKConstraint.CONSTRAINT_NAME
from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS 
    Join INFORMATION_SCHEMA.TABLE_CONSTRAINTS As UniqueConstraint
        On UniqueConstraint.CONSTRAINT_NAME = INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.UNIQUE_CONSTRAINT_NAME
    Join INFORMATION_SCHEMA.TABLE_CONSTRAINTS As FKConstraint
        On FKConstraint.CONSTRAINT_NAME = INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.CONSTRAINT_NAME        
Where UniqueConstraint.TABLE_NAME = 'TableA'

对于这些内容，您需要编写创建和删除的脚本。您需要将删除附加到顶部的删除脚本中，并将创建附加到创建脚本的末尾。

- Thomas

---

这不包含引用依赖项，这就是问题所在。 - Robert

你的意思是什么？SMS将创建一个脚本，该脚本将删除要删除的表中的所有外键，然后在最后删除该表。此外，该创建操作将创建所有外键。 - Thomas

嗯，我尝试了一下这个..然后得到了一个带有许多“IF EXISTS（SELECT * FROM sys.check_constraints WHERE”...的脚本，但是当我执行它时，仍然会出现：“无法删除对象'dbo.A'，因为它被FOREIGN KEY约束引用。” 这真的非常奇怪！ - Robert

抱歉，我看到问题了。您需要找到所有指向您想要删除的表的外键。我会修改我的回答。 - Thomas

Answer 3

在SSMS中进入数据库并右键单击。选择任务，生成脚本。然后按照您的要求设置选项（可能只选择表中的外键并创建相关对象以及删除和重新创建，我没有选项在手边，但您会看到它们）。然后选择要为其编写FK脚本的表并将其脚本化到一个文件中。打开文件并将删除语句分离到一个文件中，将创建语句分离到另一个文件中。现在您有两个文件可以在每次运行测试时自动执行所需操作。我建议在第一次运行测试之前重新创建文件（以防它们与上次运行测试时发生了更改），但不需要每个单独的测试都重新创建。

- HLGEM

---

谢谢，我非常感激。这是对我最有效的方法！ - Robert

Answer 4

在 Sql Server Management Studio 中展开表，展开 Constraints 文件夹。

记录下您拥有的任何约束条件，以便您可以重新创建它们。删除约束并删除表。重建表格并重新创建约束。

- Joe Pitz

---

我也考虑过这个，但由于许多表具有许多约束和依赖关系，这将是很多工作。（而且我试图避免的就是很多工作 :-) ） - Robert

是的，我希望微软能够让这个任务更容易执行。每次我要做这件事时，我都会大声抱怨。;-) - Joe Pitz

Answer 5

使用事务。 在测试结束时 - 回滚它。

- garik

---

好主意，但我无法获取SQL连接 - 因此无法控制事务（？）。 - Robert

Answer 6

也许可以考虑在初始化测试设置中使用虚拟服务器来维护您的数据库。启动虚拟机，进行测试，然后丢弃更改后的虚拟机。

- Larry Lustig

---

不，完全没有恢复。只需要一两分钟就可以启动虚拟机了，仅此而已。 - Larry Lustig