SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Referencing column list for foreign key no match
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SillyJon
Starting Member

2 Posts

Posted - 10/26/2013 :  00:44:01  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 10/26/2013 :  00:59:22  Show Profile  Reply with Quote
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
Go to Top of Page

SillyJon
Starting Member

2 Posts

Posted - 10/26/2013 :  01:24:10  Show Profile  Reply with Quote
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 :(

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 10/26/2013 :  04:43:11  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000