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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy