How to disable Constraints for all the tables and enable it?

sql-server sql-server-2008 foreign-keys constraints foreign-key-relationship
Out picture Out · Feb 20, 2013 · Viewed 88.5k times · Source

I have 60 tables. I want to drop 10 tables where these 10 tables are Constraints(PK,FK) to other 20 tables. While dropping these 10 tables, I need to truncate or delete data from the other 20 tables. Finally I want to disable all 60 table Constraints(FK,PK) and then enable all 60 table constraints after I am done with my work of adding/dropping tables. Is this possible?

When I drop a table it is asking for FK. When I truncate those FK dependencies it also is still showing the same. I don't want to mess with all those FK,PK.

I want to know smarter method.

Answer

Stefan Steiger picture Stefan Steiger · Feb 20, 2013 
EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"
GO

You may also want to do this:

EXEC sp_MSforeachtable @command1="ALTER TABLE ? DISABLE TRIGGER ALL"
GO

To enable them afterwards

EXEC sp_MSforeachtable @command1="ALTER TABLE ? ENABLE TRIGGER ALL"
GO

-- SQL enable all constraints - enable all constraints sql server
-- sp_MSforeachtable is an undocumented system stored procedure
EXEC sp_MSforeachtable @command1="ALTER TABLE ? CHECK CONSTRAINT ALL"
GO

Edit:
If disabling the constraints is not enough, you will have to drop the constraints.

If you're dropping and recreating the tables, you will have to recreate the foreign key constrains afterwards.

If you just need to drop the constrains, you might find this useful:
SQL DROP TABLE foreign key constraint

If you need to write a script to drop and create the constraints, you might find my post here more useful:
SQL Server: Howto get foreign key reference from information_schema?

Related questions

How to update primary key

Here is my problem - I have 2 tables: WORKER, with columns |ID|OTHER_STAF| , where ID is primary key FIRM, with columns |FPK|ID|SOMETHING_ELSE| , where combination FPK and ID make primary key, and also ID is a foreign …

Read More
SQL Multiple Foreign Keys as Primary Keys

If I declare the table below does it implicitly imply that both the foreign keys make a unique primary key or do I need to do something more to make both attributes as a primary key? CREATE TABLE Report_has_…

Read More
How to put foreign key constraints on a computed fields in sql server?

Table A has a computed field called Computed1. It's persisted and not null. Also, it always computes to an expression which is char(50). It's also unique and has a unique key constraint on it. Table B has a field RefersToComputed1, …

Read More