Enabling Constraint Silently Fails

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

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 
WITH { CHECK | NOCHECK }
CHECK CONSTRAINT ;

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.

StackExchange Database Administrators Q#111049, answer score: 8