February 2, 2012 at 12:20 pm
I can generate a forign key script in the management studio.
For example:
ALTER TABLE [Student] WITH NOCHECK ADD CONSTRAINT [FK_Student_CountryCode] FOREIGN KEY([CountryCode])
REFERENCES [Country] ([CountryCode])
GO
ALTER TABLE [Student] CHECK CONSTRAINT [FK_Student_CountryCode]
I can use this as a template to create new foreign key script.
But I don't quite understand the syntax.
I understand the first statement is to create the foreign key, what does the second statement mean?
And in the first statement, what is NoCHECK mean.
Thanks
February 2, 2012 at 1:09 pm
when you add a foreign key, all the pre-existing values are checked in the child table to make sure they exist/are valid in the referenced table.
if values exist that violate the new foreign key, an error is raised and the create foreign key fails.
nocheck skips that validation, so you could potentially keep bad values in the table (ie 0 instead of the right foreign key, or FK values that were deleted from the referenced table, but not int he child.
it might be more correct to say the NOCHECK still validates, but creates an duisabled foreign key instead i there is bad data, and a valid foreign key if the data is correct., i think.
Lowell
February 2, 2012 at 1:33 pm
Thanks, and what does the second SQL statement mean, the one check constraint.
February 2, 2012 at 2:26 pm
The first statement creates the foreign key in an untrusted state. It does this so that the key is guaranteed to be created, even if there is bad data, like the previous poster said. The SQL engine can not use this foreign key for any optimization because it is not trusted, and you are not guaranteed to have valid data.
The second statement is supposed to check the constraint to ensure that it is valid. If the check passes, SQL will now trust the constraint, the optimizer can use it, and you are guaranteed to have valid data.
I could be wrong about this, but I believe that you actually have to use the following syntax to do it correctly
ALTER TABLE [Student] WITH CHECK CHECK CONSTRAINT [FK_Student_CountryCode]
Notice that the CHECK is in there twice.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy