Generating Create script for existing foreign key through SSMS

Question

While generating the Create DB script for the existing Foreign Key constraint through SSMS, we could able to see two set of alter statements. one with NOCHECK ADD and another with CHECK.

ALTER TABLE [dbo].[claim]  WITH NOCHECK ADD  CONSTRAINT [FK_CLAIM_COPCID] 
FOREIGN KEY([copcid])
REFERENCES [dbo].[copc] ([CopcId])
GO

ALTER TABLE [dbo].[claim] CHECK CONSTRAINT [FK_CLAIM_COPCID]
GO

Why we are getting two set of scripts to create a new Constraint?.

The second script doesn't create a constraint, it literally just checks it. dba.stackexchange.com/questions/24297/… — Jacob H
– Jacob H, Commented Jun 9, 2017 at 13:05

Ryan B. · Accepted Answer · 2017-06-09 13:05:21Z

4

The first alter statement is creating a constraint and instructing that constraint to be added without checking whether the existing rows obey the new constraint.

When you add a constraint without first checking the rows, it will not be fully active until you enable it. That's what the second statement does, it enables the new constraint. If there are rows that break the constraint, you won't be able to enable it.

answered Jun 9, 2017 at 13:05

Ryan B.

3,6852 gold badges22 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Generating Create script for existing foreign key through SSMS

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related