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
Best Answer
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:
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 stateNOCHECK
here, then your constraint will be untrusted, defined byis_not_trusted = 1
insys.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
.Further reading on trusting your constraints.