3

Why would SQL Server silently fail to re-enable a foreign key constraint?

I recently attempted to re-enable a number of constraints using ALTER TABLE. Most enabled successfully; however, minority of the constrains are still reported as disabled by sys.foreign_keys.

Example:

ALTER TABLE [dbo].[Table] WITH CHECK NOCHECK CONSTRAINT FK_ConstraintName; 
-- Command(s) completed successfully.

SELECT is_disabled FROM sys.foreign_keys 
WHERE name = 'FK_Constraint' AND parent_object_id = OBJECT_ID('[dbo].[Table]');
-- returns "1", indicating that the constraint is still disabled
  • 1
    shouldn't that be WITH CHECK CHECK instead of WITH CHECK NOCHECK or is that a typo? – Tom V - try topanswers.xyz Aug 14 '15 at 13:48
  • 1
    CHECK CHECK not CHECK NOCHECK, otherwise your key is not created correctly and will be untrusted and not used by the optimiser – Mark Sinkinson Aug 14 '15 at 13:49
  • @TomV & @MarkSinkinson, unfortunately it is WITH CHECK NOCHECK. The existing data isn't under my control or mine to change. The best I can do is ensure that future data complies with the constraint. – Ben Gribaudo Aug 14 '15 at 14:08
  • 1
    @BenGribaudo see my answer. You've got these the wrong way round :-) You'll want WITH NOCHECK CHECK – Mark Sinkinson Aug 14 '15 at 14:09
8

The problem you're facing is that you are not actually re-enabling your constraint (as defined by the NOCHECK).

The syntax for re-enabling a constraint is as follows:

ALTER TABLE <tablename>
WITH { CHECK | NOCHECK }
CHECK CONSTRAINT <constraintname>;

The WITH CHECK|NOCHECK tells SQL Server whether or not you want to check the existing data in the table against the constraint. If you state NOCHECK here, then your constraint will be untrusted, defined by is_not_trusted = 1 in sys.foreign_keys.

By stating NOCHECK as the second part, you're actually saying disable the constraint.

In your case, if you do not want to check the existing data, you'll need to NOCHECK CHECK.

ALTER TABLE [dbo].[Table] WITH NOCHECK CHECK CONSTRAINT FK_ConstraintName; 

Further reading on trusting your constraints.

|improve this answer|||||
  • Thanks, @MarkSinkinson! I needed to swap the positions of my CHECK and NOCHECK. – Ben Gribaudo Aug 14 '15 at 14:13
  • 9
    I hate this syntax. How much check could a nocheck check if a nocheck could check check. – Aaron Bertrand Aug 14 '15 at 14:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.