SQL Server:与FK的Drop Table。

On table "A" depend about 30 other tables via FK to "A.Id".

在表A上，“A”通过FK到“A.Id”依赖于大约30个其他表。

For integration testing I have to drop the table and recreate it to create a defined state. Because of the dependent objects their seem to be no way to delete and recreate the table. The error message is:

对于集成测试，我必须删除表并重新创建它以创建一个定义的状态。由于依赖对象的存在，它们似乎无法删除和重新创建表。错误信息是:

Could not drop object 'dbo.A' because it is referenced by a FOREIGN KEY constraint

不能删除对象的dbo。因为它是由外键约束引用的。

Question(s):

问题(s):

How can I drop and recreate table "A"?
如何删除并重新创建“A”表?
(or) is there any way turn the schema dependencies off globally?
(或者)是否有方法在全局范围内关闭模式依赖关系?
(or) is there any way to backup (all!) dependencies before deleting and restoring table "A" and restore all dependencies afterward?
(或)在删除和恢复表“A”并恢复所有依赖项之前，有什么方法可以备份(所有!)依赖项?

6 个解决方案

#1

Go to the database in SSMS and right click. Choose tasks, generate scripts. Then go through the options and set them the way you want (Probaly to only choose foreign keys inthe table and create dependant objects and drop and recreate, dont;hve the options in front of me but you will see them. THen choose the tables you want to script the FKs for and script them to a file. Open the file and separate the drop statements into one file and the create statments into another. Now you have tweo files you can run do autmatically do what you want when ever you run run a test. I would suggest recreating the files before running the first test (in case they have changed since the last time tests were run) but not for each individual test.

进入SSMS中的数据库并右击。选择任务,生成脚本。然后遍历选项并按您想要的方式设置它们(通常只在表中选择外键并创建依赖对象并删除和重新创建，不要;在我面前有选项，但您会看到它们。然后选择要脚本的表，并将它们脚本编写到一个文件中。打开文件并将drop语句分离到一个文件中，将create语句分离到另一个文件中。现在您有了tweo文件，可以在运行测试时自动执行所需的操作。我建议在运行第一个测试之前重新创建这些文件(以防它们自上次测试运行以来发生了更改)，但不是针对每个单独的测试。

#2

Explore the sys.foreign_key_columns system table. Here's an example that I had laying around that will, given a table, tells you which of it's columns are keyed to another table:

探索系统。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)

With this, or some variation there-of, you could find out the foreign keys, drop them, do your stuff, and then re-create the foreign keys.

有了这个，或者一些变化，你可以找到外键，放下它们，做你的事情，然后重新创建外键。

I should add that I know this works on SQL2005 and SQL2008. I don't really know if it will work on SQL2000/MSDE.

我应该补充一点，我知道这适用于SQL2005和SQL2008。我不知道它是否适用于SQL2000/MSDE。

#3

In Management Studio, you can right-click on the table and script the CREATE and the DROP which will include all of the foreign keys.

在Management Studio中，您可以右键单击该表，并编写CREATE和DROP的脚本，其中将包含所有外键。

To be more specific, this will give you all constraints on which your Table depends. However, it does not give you the list of foreign keys that depend on this table. So, in addition to the scripts you would generate by right-clicking on the table in SMS, you need to find and script all the foreign keys. To get a list of them, you can run a query like so:

更具体地说，这将为您提供表所依赖的所有约束。但是，它没有给出依赖于该表的外键的列表。因此，除了通过在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'

For each one of these, you'll need to script the create and drop. You would append the drops to the top of your drop script and the creates at the end of your create script.

对于其中的每一个，您都需要编写创建和删除的脚本。您可以将drop追加到drop脚本的顶部，并在create脚本的末尾创建。

#4

Expand the table in Sql Server Management Studio, Expand the Constraints folder.

展开Sql Server Management Studio中的表，展开约束文件夹。

Write down any constraints that you have so you can re-create them. Delete the constraints and drop the table. Rebuild the table and re-create your constraints.

写下你所拥有的任何约束，这样你就可以重新创建它们。删除约束并删除表。重新构建表并重新创建约束。

#5

Use transaction. At the end of test - rollback it.

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

#6

Perhaps consider maintaining a virtual server with your database in its initialize test setup. Boot the VM, perform your testing, then throw away the changed VM.

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

#1

#2