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

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.

Answer 2

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

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.

Answer 3

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

Answer 4

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 5

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.

Answer 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.

Answer 7

Or check this link