Sharing the information you need to get development done!
Welcome to DevAuthority.Com Sign in | Join | Help
in Search

gmontrone

Disabling Foreign Keys and Triggers - Sql Server 2005

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.

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.
3) Use with caution

Published Thursday, November 02, 2006 2:01 PM by gmontrone

Comments

No Comments
Anonymous comments are disabled







This Blog

Post Calendar

<November 2006>
SuMoTuWeThFrSa
2930311234
567891011
12131415161718
19202122232425
262728293012
3456789

Post Categories

Syndication



Powered by Community Server, by Telligent Systems