August 22, 2012 at 4:48 am
Hi All
Why does the last statement in the following batch fail ?
create table parent (Col1 int not null, col2 int not null , col3 int not null)
go
alter table parent add constraint PK primary key (Col1, col2)
go
create table child (Col1 int not null, col2 int not null , col3 int not null)
go
alter table child add constraint FK foreign key (Col1) references parent(Col1)
I get the following error: Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 'parent' that match the referencing column list in the foreign key 'FK'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
Thanks
August 22, 2012 at 5:10 am
Col1 needs to have a PRIMARY KEY, UNIQUE constraint, or (less acceptably) a UNIQUE index defined on it. Something needs to enforce uniqueness on col1 alone. The composite primary key on (col1, col2) allows duplicates of col1 values e.g. (1, 2) and (1, 3).
August 22, 2012 at 5:21 am
SQL Kiwi (8/22/2012)
Col1 needs to have a PRIMARY KEY, UNIQUE constraint, or (less acceptably) a UNIQUE index defined on it. Something needs to enforce uniqueness on col1 alone. The composite primary key on (col1, col2) allows duplicates of col1 values e.g. (1, 2) and (1, 3).
Thanks
So if the Primary Key has 2 columns defined, my foreign key needs to have the same 2 Columns defined?
Thanks
August 22, 2012 at 5:24 am
SQLSACT (8/22/2012)
So if the Primary Key has 2 columns defined, my foreign key needs to have the same 2 Columns defined?
Yes. The point is that each child record must be guaranteed to have only one parent.
August 22, 2012 at 5:26 am
SQL Kiwi (8/22/2012)
SQLSACT (8/22/2012)
So if the Primary Key has 2 columns defined, my foreign key needs to have the same 2 Columns defined?Yes. The point is that each child record must be guaranteed to have only one parent.
Understood - Thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply