MS SQL – disable constraint to update/delete records

30 Jan

In the relational db you have many relations, and in order to delete a certain record you have to delete all of the relations that record have in all the referenced tables. One way is to temporary disable the constraints – update the records – enable the constraints. You can use the command below:

* Disable constraints for all tables:
EXEC sp_msforeachtable "ALTER TABLE XX NOCHECK CONSTRAINT all"

* Re-enable constraints for all tables:
EXEC sp_msforeachtable "ALTER TABLE XX WITH CHECK CHECK CONSTRAINT all"


* Disable the constraints on a table called tableName:
ALTER TABLE tableXX NOCHECK CONSTRAINT ALL

* Re-enable the constraints on a table called tableName:
ALTER TABLE tableXX WITH CHECK CHECK CONSTRAINT ALL

Read more:
http://stackoverflow.com/questions/737115/turn-off-constraints-temporarily-ms-sql
http://stackoverflow.com/questions/159038/can-foreign-key-constraints-be-temporarily-disabled-using-t-sql/161410#161410


Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: