error when creating FK constraint

  • I am trying to create a FK constraint and i get an error after excuting it. any details on this would be greatly appreciated

    This is the script

    ALTER TABLE [dbo].[XXX] ADD

    CONSTRAINT [FK_ATC_DIM_ATC_LVL_4_CD] FOREIGN KEY

    (

    [ATC_LVL_4_CD]

    ) REFERENCES [dbo].[RDM_ATC_LVL_4_LKUP] (

    [ATC_LVL_4_CD]

    )

    error coming up is

    ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_ATC_DIM_ATC_LVL_4_CD'. The conflict occurred in database 'dRDM_Dispensed_Rx_V2', table 'RDM_ATC_LVL_4_LKUP', column 'ATC_LVL_4_CD'.

  • Make sure your referencing column in the master table is Primary Key, same data type and the size.

    Shas3

  • Yes everything is consistent. The datatype, colwidth and colname. I tried dropping the same contraint thinking that it might be existing but it wasnt. any other thoughts

  • Try the Following one

    ALTER TABLE [dbo].[XXX] ADD

    CONSTRAINT [FK_ATC_DIM_ATC_LVL_4_CD] FOREIGN KEY REFERENCES [dbo].[RDM_ATC_LVL_4_LKUP] (

    [ATC_LVL_4_CD])

    Shas3

  • You've probably got a record in table XXX with a value in the ATC_LVL_4_CD column that's not in the RDM_ATC_LVL_4_LKUP.ATC_LVL_4_CD column.

    use this query to find out if this is true:

    select xxx.* from xxx

    left outer join RDM_ATC_LVL_4_LKUP on xxx.ATC_LVL_4_CD = RDM_ATC_LVL_4_LKUP.ATC_LVL_4_CD

    where RDM_ATC_LVL_4_LKUP.ATC_LVL_4_CD is null

    This will identify any records in the table that don't have a matching value in the referenced table.

    Steve Armistead,

    Database Administration

    Panther Systems Northwest, Inc.

    Vancouver, WA

    Steve

  • This is the error i get when running the above

    Number of referencing columns in foreign key differs from number of referenced columns, table 'dbo.RDM_ATC_DIM'.

  • Okay,

    The PK of table RDM_ATC_LVL_4_LKUP (or a unique index) has to be on the ATC_LVL_4_CD column. Each rcd in this table has to be unique by ATC_LVL_4_CD for you to be able to create this fkey constraint.

    Steve Armistead,

    Database Administration

    Panther Systems Northwest, Inc.

    Vancouver, WA

    Steve

  • quote:


    You've probably got a record in table XXX with a value in the ATC_LVL_4_CD column that's not in the RDM_ATC_LVL_4_LKUP.ATC_LVL_4_CD column.

    use this query to find out if this is true:

    select xxx.* from xxx

    left outer join RDM_ATC_LVL_4_LKUP on xxx.ATC_LVL_4_CD = RDM_ATC_LVL_4_LKUP.ATC_LVL_4_CD

    where RDM_ATC_LVL_4_LKUP.ATC_LVL_4_CD is null

    This will identify any records in the table that don't have a matching value in the referenced table.

    Steve Armistead,

    Database Administration

    Panther Systems Northwest, Inc.

    Vancouver, WA


  • Thanks soo much that really helped and indeed there was a row which wasnt there in the referenced table.

    thanks again

Viewing 9 posts - 1 through 8 (of 8 total)

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