June 24, 2003 at 9:32 am
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'.
June 24, 2003 at 9:44 am
Make sure your referencing column in the master table is Primary Key, same data type and the size.
Shas3
June 24, 2003 at 9:47 am
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
June 24, 2003 at 9:59 am
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
June 24, 2003 at 10:01 am
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
June 24, 2003 at 10:06 am
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'.
June 24, 2003 at 10:16 am
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
June 24, 2003 at 10:19 am
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
June 24, 2003 at 10:20 am
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