Referencing column list for foreign key no match

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Referencing column list for foreign key no match

Author

Topic

SillyJon
Starting Member

2 Posts

Posted - 2013-10-26 : 00:44:01

Hey guys, it's my first time using this website :)

Here's my problem. I'm encountering this very weird problem,
so I create a staff table
CREATE TABLE Staff (
staffNo numeric(10),
venueNo numeric(10),
name nvarchar(20),
DOB datetime,
position nvarchar(20),
salary numeric(8,2)
CONSTRAINT staff_PK PRIMARY KEY(staffNo, venueNo),
CONSTRAINT venue_FK FOREIGN KEY(venueNo) REFERENCES Venue
);

and then
when I create a professional therapist table
CREATE TABLE Professional_Therapist (
staffNo numeric(10),
specialization nvarchar(20),
bonus numeric(8,2),
CONSTRAINT professional_PK PRIMARY KEY(staffNo),
CONSTRAINT staff_FK FOREIGN KEY(staffNo) REFERENCES Staff
);
it says
The number of columns in the referencing column list for foreign key 'staff_fk' does not match those of the primary key in the referenced table 'Staff'.

Looking forward to your replies :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-26 : 00:59:22

the foreign key statements are not correct. It should specify the target column also


CREATE TABLE Staff (
staffNo numeric(10),
venueNo numeric(10),
name nvarchar(20),
DOB datetime,
position nvarchar(20),
salary numeric(8,2)
CONSTRAINT staff_PK PRIMARY KEY(staffNo, venueNo),
CONSTRAINT venue_FK FOREIGN KEY(venueNo) REFERENCES Venue (venueNo)
);

and then
when I create a professional therapist table
CREATE TABLE Professional_Therapist (
staffNo numeric(10),
specialization nvarchar(20),
bonus numeric(8,2), 
CONSTRAINT professional_PK PRIMARY KEY(staffNo),
CONSTRAINT staff_FK FOREIGN KEY(staffNo) REFERENCES Staff (staffNo)
);

I'm assuming venueNo is column name in Venue table to which you want to set FK relationship to

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

SillyJon
Starting Member

2 Posts

Posted - 2013-10-26 : 01:24:10

Thanks for your swift reply, but this did not fix the problem :(

Just for note. I created the Staff first so I executed it.
Then I created the professional therapist table then executed it and then I got the problem.

Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 'Staff' that match the referencing column list in the foreign key 'staff_FK'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

So the problem is still there :(

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-26 : 04:43:11

quote:
Originally posted by SillyJon

Thanks for your swift reply, but this did not fix the problem :(

Just for note. I created the Staff first so I executed it.
Then I created the professional therapist table then executed it and then I got the problem.

Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 'Staff' that match the referencing column list in the foreign key 'staff_FK'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

So the problem is still there :(

nope this is different
Did you try creating StaffNo as a primary key in Staff table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources