How to Disable Foreign Keys and Triggers

Sometimes it's just necessary to get rid of foreign key constraints and triggers in order to do maintenance on a database.  Imagine trying to delete all records from 100+ tables, but you now have to put them in dependency-order in order to do it.  While foreign key constraints help maintain relational integrity, it can be a huge pain in the rear when you are trying to do basic operations.  Of course doing this should only be done when you know-for-sure that you won't ruin the overall integrity of the database.

Disable all Constraints on a table

ALTER TABLE yourtable NOCHECK CONSTRAINT ALL

This will disable, but not remove all of your constraints. It will not, however, remove Primary Key and uniqueness constraints.  If you want to only remove a specific constraint, you can replace the word ALL with the name of the constraint - no quotes.  To re-enable replace NOCHECK with CHECK
Sometimes we need to disable triggers because they just get in our way.


To Enable all Constraints

ALTER TABLE yourtable WITH CHECK
CHECK CONSTRAINT ALL


To disable all triggers on a table:

DISABLE Trigger ALL ON yourtable

Replace the word ALL with a specific trigger name to disable a specific trigger.

Some notes:
1) Trigger enabling/disabling is only available for Sql Server 2005
2) In order to truncate a table, you must DROP foreign key constraints rather than disable them.  Use Delete and change your identity seed with DBCC CHECKIDENT if that will solve your problem.

You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint; instead, use DELETE statement without a WHERE clause. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
TRUNCATE TABLE may not be used on tables participating in an indexed view.