Create foreign key script

  • 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

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Thanks, and what does the second SQL statement mean, the one check constraint.

  • 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