Turn Off Constraints Question

Question

How can I temporarily turn off constraints, run a batch process then turn them back on?  Can I do this at the database level, or do I have to do it for each table?

Thanks

---

Thanks,
Corey Furman
Corey Furman's Facebook profile
______________________________________________________
I just know that something good is gonna happen,
I don't know when, but just saying it could even make it happen.
______________________________________________________
Please mark posts as answer or helpful when they are.

Answer 1

There's no database or even table level setting for this. You need to work at the constraint level. Can only be done for FK and CHECK constraints. Something like:

ALTER TABLE t NOCHECK CONSTRAINT c

And to enable it again, two versions:

ALTER TABLE t CHECK CONSTRAINT c
ALTER TABLE t WITH CHECK CHECK CONSTRAINT c
You can see whether for instance a check constraint is enabled in sys.check_constraints

The first one leaves the constraint non-trusted since it is added without checking the data. Non-trusted constraint has its drawbacks (one such is discussed in below blog posts).
http://sqlblog.com/blogs/tibor_karaszi/archive/2008/01/12/non-trusted-constraints.aspx
http://sqlblog.com/blogs/tibor_karaszi/archive/2008/01/12/non-trusted-constraints-and-performance.aspx

---

Tibor Karaszi

Answer 2

Thanks.  I need to update a dozen or so tables once a week between two dissimilar systems.  I know that the final state of the batch is correct, but the deleting/inserting ripples back and forth over the tables and takes a few minutes.  It would be nice if I could relax the constraints preceeding the execution.  I can always script the dependencies, but I was hoping there was a shortcut.

Thanks.

---

Thanks,
Corey Furman
Corey Furman's Facebook profile
______________________________________________________
I just know that something good is gonna happen,
I don't know when, but just saying it could even make it happen.
______________________________________________________
Please mark posts as answer or helpful when they are.

Answer 3

Will the database still need to be updated as normal by interactive users while this process takes place?

Answer 4

Nope.  Got an idea?

---

Thanks,
Corey Furman
Corey Furman's Facebook profile
______________________________________________________
I just know that something good is gonna happen,
I don't know when, but just saying it could even make it happen.
______________________________________________________
Please mark posts as answer or helpful when they are.

Answer 5

As long as the answer is "nope" you are good to go. 

:-)

Answer 6

I guess I probably should have mentioned that, but I figured it was intuitive.  I want to turn off the constraints for me, not the end users.  They can barely be trusted as it is :P

---

Thanks,
Corey Furman
Corey Furman's Facebook profile
______________________________________________________
I just know that something good is gonna happen,
I don't know when, but just saying it could even make it happen.
______________________________________________________
Please mark posts as answer or helpful when they are.