Given the following Foreign Key Constraint definition inline of a Create Table definition in a SSDT project:
CREATE TABLE A
(...Columns...),
CONSTRAINT [FK_O] FOREIGN KEY ([OID]) REFERENCES [dbo].[O] ([ID]) ON DELETE CASCADE NOT FOR REPLICATION,
(...)
and the following quote from MSDN
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.
, I wonder why the FIRST deployment result of this NEW table does look like that using a constraint WITH NOCHECK option as follows:
ALTER TABLE [dbo].[A] WITH NOCHECK ADD CONSTRAINT [FK_O] FOREIGN KEY([OID])
REFERENCES [dbo].[O] ([ID])
ON DELETE CASCADE
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[A] CHECK CONSTRAINT [FK_O]
GO
So the question is, shouldn't the inline Constraint definition that does not allow to define "with Nocheck" paired with the rule from the MSDB page result into a constraint using the WITH CHECK option on this NEW table? Why do we get a WITH NOCHECK Constraint here?
Best Answer
NOT FOR REPLICATION
means that the constraint is never checked, even if you check it explicitly.Probably, SSDT here is making explicit something that is implicit.