Post reply

Foreign key referencing a unique constraint

  • December 14, 2006 at 1:31 pm

    #176050

    I'm trying to establish an FK relationship between a table, B, and a parent table ,A.  referencing a field in A which is not it's primary key, but is constrained to be unique.  According to the BOL, this should be possible, I'm using the following script:

    ALTER

    TABLE [dbo]. ADD CONSTRAINT [FK_B_A] FOREIGN KEY([SHPK])

    REFERENCES

    [dbo].[A] ([ShPk]

    but I keep getting the following error:

    Msg 547, Level 16, State 0, Line 1

    The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_B_A" The conflict occurred in database "XYZ", table "dbo.A", column 'shpk'.

    what am I doing wrong?

    Thanks

  • December 14, 2006 at 1:40 pm

    #678201

    It means that you have orphaned data in the child table.  Use a left join to find which rows they are.  Then you'll have to decide wether you want to recreate the parents, delete the childs or archive them to another table.

  • December 14, 2006 at 2:48 pm

    #678219

    That did it. Thanks!

Viewing 3 posts - 1 through 2 (of 2 total)

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