SQL Server: Drop Table with FK

Question

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

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

Question(s):

• How can I drop and recreate table "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?

Answer 1

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:

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.

Answer 2

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.

---

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:

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.