locked
Set-Off Foreign Keys RRS feed

  • Question

  • Is there a way to temporaly set off foreign keys dependencies (like a sql command or something...) and then set them on again? I've to migrate 90 tables on my app, but i get the "dependencies error", any ideas?

    Thursday, February 28, 2008 8:19 PM

Answers

  • Be very careful how you enable the constraints once your work is done.

    Because BOL is very confusing and fuzzy on the syntax!

     

    BOL says in the section on ALTER TABLE:

    WITH CHECK | WITH NOCHECK

    Specifies whether the data in the table is or is not validated against a newly added or re-enabled FOREIGN KEY or CHECK constraint. If not specified, WITH CHECK is assumed for new constraints, and WITH NOCHECK is assumed for re-enabled constraints.

    If you do not want to verify new CHECK or FOREIGN KEY constraints against existing data, use WITH NOCHECK. We do not recommend doing this, except in rare cases. The new constraint will be evaluated in all later data updates. Any constraint violations that are suppressed by WITH NOCHECK when the constraint is added may cause future updates to fail if they update rows with data that does not comply with the constraint.

    The query optimizer does not consider constraints that are defined WITH NOCHECK. Such constraints are ignored until they are re-enabled by using ALTER TABLE table CHECK CONSTRAINT ALL.

    -- end BOL --

    Note the last paragraph about the optimizer not considering 'nochecked constraints'.

    What this means is that when you disable a check constraint, that constraint also then becomes 'untrusted'.

    The optimizer ignores constraint it can't trust. (that is very logical).

    However, BOL says that for the optimizer to consider a (untrusted) constraint again, one should use

     

    ALTER TABLE table CHECK CONSTRAINT ALL (or by name, ALTER TABLE table CHECK CONSTRAINT constraint)

     

    This is however not working as described. The constraint is indeed enabled, but still untrusted.

    It's a fast command, but that's because existing data isn't being validated.

     

    What you should use is:

    ALTER TABLE table WITH CHECK CHECK CONSTRAINT ALL

    (or by name, ALTER TABLE table WITH CHECK CHECK CONSTRAINT constraint)

     

    Only then is the constraint validating existing data, and also - if validation is succesful - the constraint in a trusted state again.

     

    The check a constraint if it's trusted or not:

     

    SELECT OBJECTPROPERTY( OBJECT_ID( 'constraint'), 'CnstIsNotTrusted')

     

    1 = True
    0 = False

     

    /Kenneth

     

    Friday, February 29, 2008 9:07 AM

All replies

  • Code Snippet

    ALTER TABLE MyTable NOCHECK CONSTRAINT MyConstraint

     

     

     

    Or for all of the foreign key constraints in the database:

     

    Code Snippet

    SELECT 'ALTER TABLE ' + OBJECT_NAME(id) + ' NOCHECK CONSTRAINT ' + OBJECT_NAME(constid) FROM sysCONSTRAINTS

    WHERE OBJECT_NAME(constid) LIKE (whatever your naming convention is for foreign key constraints)

     

     


     

     

    Thursday, February 28, 2008 8:38 PM
  • Be very careful how you enable the constraints once your work is done.

    Because BOL is very confusing and fuzzy on the syntax!

     

    BOL says in the section on ALTER TABLE:

    WITH CHECK | WITH NOCHECK

    Specifies whether the data in the table is or is not validated against a newly added or re-enabled FOREIGN KEY or CHECK constraint. If not specified, WITH CHECK is assumed for new constraints, and WITH NOCHECK is assumed for re-enabled constraints.

    If you do not want to verify new CHECK or FOREIGN KEY constraints against existing data, use WITH NOCHECK. We do not recommend doing this, except in rare cases. The new constraint will be evaluated in all later data updates. Any constraint violations that are suppressed by WITH NOCHECK when the constraint is added may cause future updates to fail if they update rows with data that does not comply with the constraint.

    The query optimizer does not consider constraints that are defined WITH NOCHECK. Such constraints are ignored until they are re-enabled by using ALTER TABLE table CHECK CONSTRAINT ALL.

    -- end BOL --

    Note the last paragraph about the optimizer not considering 'nochecked constraints'.

    What this means is that when you disable a check constraint, that constraint also then becomes 'untrusted'.

    The optimizer ignores constraint it can't trust. (that is very logical).

    However, BOL says that for the optimizer to consider a (untrusted) constraint again, one should use

     

    ALTER TABLE table CHECK CONSTRAINT ALL (or by name, ALTER TABLE table CHECK CONSTRAINT constraint)

     

    This is however not working as described. The constraint is indeed enabled, but still untrusted.

    It's a fast command, but that's because existing data isn't being validated.

     

    What you should use is:

    ALTER TABLE table WITH CHECK CHECK CONSTRAINT ALL

    (or by name, ALTER TABLE table WITH CHECK CHECK CONSTRAINT constraint)

     

    Only then is the constraint validating existing data, and also - if validation is succesful - the constraint in a trusted state again.

     

    The check a constraint if it's trusted or not:

     

    SELECT OBJECTPROPERTY( OBJECT_ID( 'constraint'), 'CnstIsNotTrusted')

     

    1 = True
    0 = False

     

    /Kenneth

     

    Friday, February 29, 2008 9:07 AM
  • Thanks for your help, it help me a lot.

     

    Friday, February 29, 2008 2:38 PM