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
 Error 1776 when creating tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mechk
Starting Member

3 Posts

Posted - 01/30/2013 :  12:17:31  Show Profile  Reply with Quote
Heya

I tried to create some tables and got Error 1776. I've read about it but can't seem to find out why it doesn't work.

This is the code for the tables "involved"

create table Employee
(SocialSecurity varchar(12) not null,
Name varchar(20),
City varchar(10),
Street varchar(20),
ZipCode varchar(5),
Email varchar(20),
Phone varchar(20),
constraint Employee_PK primary key(SocialSecurity))

create table Instructor
(InstID varchar(5) not null,
SocialSecurity varchar(12) not null,
constraint Instructor_PK primary key(InstID,SocialSecurity),
constraint SocialSecurity_FK foreign key(SocialSecurity)
references Employee(SocialSecurity)
)

create table Workout
(WorkID varchar(5) not null,
Name varchar(15),
Date varchar(11),
Time varchar(5),
Length varchar(5),
Level varchar(10),
constraint Workout_PK primary key(WorkID))

create table InstructorWorkout
(InstID varchar(5) not null,
WorkID varchar(5) not null,
Date varchar(11),
constraint InstructorWorkout_PK primary key(InstID,WorkID),
constraint InstID_InsWor_FK foreign key(InstID) 
references Instructor(InstID),
constraint WorkID_FK foreign key(WorkID) 
references Workout(WorkID))


The error message:
There are no primary or candidate keys in the referenced table 'Instructor' that match the referencing column list in the foreign key 'InstID_InsWor_FK'.

I also read that FK should reference to all the PK (meaning both InstID,SocialSecurity) but if I try with:

create table InstructorWorkout
(InstID varchar(5) not null,
WorkID varchar(5) not null,
Date varchar(11),
constraint InstructorWorkout_PK primary key(InstID,WorkID),
constraint InstID_InsWor_FK foreign key(InstID,SocialSecurity) 
references Instructor(InstID,SocialSecurity),
constraint WorkID_FK foreign key(WorkID) 
references Workout(WorkID))


I get a different error (1769):
Foreign key 'InstID_InsWor_FK' references invalid column 'SocialSecurity' in referencing table 'InstructorWorkout'.

James K
Flowing Fount of Yak Knowledge

3760 Posts

Posted - 01/30/2013 :  12:23:07  Show Profile  Reply with Quote
You also have to have the SocialSecurity Column in the InstructorWorkout table.
create table InstructorWorkout
(InstID varchar(5) not null,
SocialSecurity varchar(12) not null,
WorkID varchar(5) not null,
Date varchar(11),
constraint InstructorWorkout_PK primary key(InstID,WorkID),
constraint InstID_InsWor_FK foreign key(InstID,SocialSecurity) 
references Instructor(InstID,SocialSecurity),
constraint WorkID_FK foreign key(WorkID) 
references Workout(WorkID))
Go to Top of Page

mechk
Starting Member

3 Posts

Posted - 01/30/2013 :  12:26:41  Show Profile  Reply with Quote
Thank you for the quick response!

Do I have to have SocialSecurity as a PK in InstructorWorkout aswell? Or is it enough to just add it as a column?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3760 Posts

Posted - 01/30/2013 :  12:42:50  Show Profile  Reply with Quote
That really depends on the data - if the combination of InstID and WorkID would be unique, you can keep just those two as the primary key from a data integrity perspective. But if there can be multiple SocialSecurities for a given combination of InstID and WorkID, you would certainly need to add SocialSecurity to the PK.

Go to Top of Page

mechk
Starting Member

3 Posts

Posted - 01/30/2013 :  13:19:46  Show Profile  Reply with Quote
Thanks alot for the clarification
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.05 seconds. Powered By: Snitz Forums 2000