Interesting behavior causing: "There are no primary or candidate keys in the referenced table that match the referencing column"  
Author Message
Jonathan MacCollum





PostPosted: .NET Framework Data Access and Storage, Interesting behavior causing: "There are no primary or candidate keys in the referenced table that match the referencing column" Top

So I was creating the scripts to create/drop my database tables that I'll be syncing over to a sql express client from oracle when I kept getting an error trying to add a foreign key over two existing tables.

table_a

key01 bigint,
key02 int,
col03 varchar,
col04 datetime

primary key: key02, key01

table_b

col01 bigint,
fkey01 bigint,
fkey02 int,
col03 datetime

table_b references table_a such that:

- [table_b].[fkey01] maps to [table_a].[key01] and
- [table_b].[fkey02] maps to [table_a].[key02]

however I try to add my constraint:

ALTER TABLE [dbo].[table_b] WITH CHECK ADD CONSTRAINT [FK_table_b_table_a] FOREIGN KEY([fkey01], [fkey02])
REFERENCES
[dbo].[table_a] ([key01], [key02])

It returns this error: "There are no primary or candidate keys in the referenced table that match the referencing column."

Whats interesting here is the order in which the primary key is set on table_a. Aparantly the order in which the columns that make up the primary key in table_a (in this case key02 then key01) must be mapped to the order in which the columns in the foreign key are defined.

You can either re-order the columns in the primary key or re order the columns in your foreign key to get this error to disapear.

ALTER TABLE [dbo].[table_b] WITH CHECK ADD CONSTRAINT [FK_table_b_table_a] FOREIGN KEY([fkey02], [fkey01])
REFERENCES
[dbo].[table_a] ([key02], [key01])

I found it rather interesting so I thought I would share. :)




.NET Development10