create forign key statement

Question

create forign key statement

sqlfriend

SSC Guru

Points: 52781
More actions
March 30, 2012 at 2:26 pm

#271254

whenever I generate SQL statement of Forign key I got something like the below statement:
ALTER TABLE [StudentProgram] WITH NOCHECK ADD CONSTRAINT [FK_StudentProgram_StudentProgramExitReason] FOREIGN KEY([ExitReasonCode])
REFERENCES StudentProgramExitReason] ([ExitReasonCode])
GO
ALTER TABLE [StudentProgram] CHECK CONSTRAINT [FK_StudentProgram_StudentProgramExitReason]
GO
I know the first statement is to add a FK constraint without checking exising data.
What does the second statement do?
Thanks

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

From Books Online:

{ CHECK | NOCHECK } CONSTRAINT
Specifies that constraint_name is enabled or disabled. This option can only be used with FOREIGN KEY and CHECK constraints. When NOCHECK is specified, the constraint is disabled and future inserts or updates to the column are not validated against the constraint conditions. DEFAULT, PRIMARY KEY, and UNIQUE constraints cannot be disabled.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlfriend SSC Guru Points: 52781 More actions · Answer 2

Sorry, I still don't understand it.

The first is to add constraints without checking existing data.

What does the second do, check constraint??

Why first one not check, the second check...

sqlfriend SSC Guru Points: 52781 More actions · Answer 3

Or does the first statement mean we add constraints without checking existing data.

the second mean for future new records, check constraints?

Is that right?

Thanks

seth delconte SSCertifiable Points: 6388 More actions · Answer 4

I assume that using the two statements together is kind of a safety mechanism; creating the FK WITH NO CHECK guarantees that the creation of the constraint will not be hampered by existing invalid data, and then an attempt to activate the constraint is issued via the second statement.

_________________________________
seth delconte
http://sqlkeys.com

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

The first one is how the FK is currently defined. The nocheck there tells it to not check existing data.

The second statement enables the foreign key.

Does that help?

Gail - correct me where I am wrong.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

sqlfriends (3/30/2012)
Sorry, I still don't understand it.
The first is to add constraints without checking existing data.
What does the second do, check constraint??
Why first one not check, the second check...

As per Books Online, the first creates the constraint WITH NOCHECK

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.

With NoCheck means the constraint is not evaluated against existing data.

Second alters the constraint, enabling and checking the existing data (as per previous quote).

Two statements so that the constraint will always be created, it just might fail to be checked. In that case new data will be checked but old will not, allowing the developer to clean up the bad data and then manually check the constraint.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlfriend SSC Guru Points: 52781 More actions · Answer 7

With NoCheck means the constraint is not evaluated against existing data.
Second alters the constraint, enabling and checking the existing data (as per previous quote).
Two statements so that the constraint will always be created, it just might fail to be checked. In that case new data will be checked but old will not, allowing the developer to clean up the bad data and then manually check the constraint.

Thank you, but I still confuse about the above two underlined statement ?

The first underlined statement says it is checking existing data, the second says new data will be checked but old will not?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

sqlfriends (3/30/2012)
With NoCheck means the constraint is not evaluated against existing data.
Second alters the constraint, enabling and checking the existing data (as per previous quote).
Two statements so that the constraint will always be created, it just might fail to be checked. In that case new data will be checked but old will not, allowing the developer to clean up the bad data and then manually check the constraint.
Thank you, but I still confuse about the above two underlined statement ?
The first underlined statement says it is checking existing data, the second says new data will be checked but old will not?

No they don't contradict each other. Read the second statement in context.

Gail said that should there be a failure in checking the old data, then the second statement will fail which means what is underlined in the second statement.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

sqlfriend SSC Guru Points: 52781 More actions · Answer 9

Thanks all,

So the first statement is to insure the constraints can be created.

The second one enables the foreign key and will check both exisiting and new future data.

If it finds -- for example a forign key violation of the existing data, it will give an error.

Correct?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

Yes

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

sqlfriend SSC Guru Points: 52781 More actions · Answer 11

sqlfriend

SSC Guru

Points: 52781

March 30, 2012 at 3:07 pm

#1467217

Thanks all.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

sqlfriends (3/30/2012)
Thanks all,
So the first statement is to insure the constraints can be created.
The second one enables the foreign key and will check both exisiting and new future data.
If it finds -- for example a forign key violation of the existing data, it will give an error.
Correct?

Almost. The first statement will ensure that new data is checked. With that, any new rows that don't meet the constraint will fail

The second will go back and check existing data to ensure it conforms with the constraint and marks the constraint as trusted (all data does conform to it)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlfriend SSC Guru Points: 52781 More actions · Answer 13

Almost. The first statement will ensure that new data is checked. With that, any new rows that don't meet the constraint will fail
The second will go back and check existing data to ensure it conforms with the constraint and marks the constraint as trusted (all data does conform to it)

Thanks, so can I rephrase like below:

The first statement ensures the constraints to be created without checking existing data.

If we don't add the second statement below it, that means, the constraint is created and enabled, and will check future new records to see if they violate the constraints.

The second statement is to go back to check existing data and also will check for future data.

Thanks