How to catch a double SQL error when deploying a Foreign Key

OzzieOzzie Posts: 46 Bronze 5
edited March 23, 2011 3:19PM in SQL Packager Previous Versions
Here's an instance.

If you go to add a Foreign key to a child table where the referenced column in the parent table is not a primary key, you will receive 2 errors.

Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 'Mosaic.dbo.ProgramRecoType' that match the referencing column list in the foreign key 'FK_ProviderServiceCountryRecommendation_ProgramRecoType_ProgramRecoType_ID_ProgramRecoType_ID'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

If you try/catch - you only catch the second error - resulting in the display of
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

Which does me no good? Is there actually a way to catch this when deploying scripts?

Doug

Comments

  • James BJames B Posts: 1,124 Silver 4
    Thanks for posting.

    I'm guessing you're building a C# project from packager and you want to be able to catch the first error in that?

    I'm not immediately sure off the top of my head. I imagine it's something to do with the way SQL Server executes the statements and returns the errors. You might want to see if the InnerException contains anything further... but I'll see if I can set up a replication here to try it out on myself
    Systems Software Engineer

    Redgate Software

Sign In or Register to comment.