none
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.
    Monday, February 02, 2009 4:33 PM

Answers

All replies

  • 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
    Monday, February 02, 2009 4:56 PM
    Moderator
  • 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.
    Monday, February 02, 2009 5:20 PM
  • Will the database still need to be updated as normal by interactive users while this process takes place?
    Monday, February 02, 2009 5:24 PM
    Moderator
  • 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.
    Monday, February 02, 2009 5:54 PM
  • As long as the answer is "nope" you are good to go. 

    :-)
    Monday, February 02, 2009 7:14 PM
    Moderator
  • 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.
    Monday, February 02, 2009 7:18 PM