Error 1776 when creating tables

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

Error 1776 when creating tables

Author

Topic

mechk
Starting Member

3 Posts

Posted - 2013-01-30 : 12:17:31

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-30 : 12:23:07

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))

mechk
Starting Member

3 Posts

Posted - 2013-01-30 : 12:26:41

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?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-30 : 12:42:50

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.

mechk
Starting Member

3 Posts

Posted - 2013-01-30 : 13:19:46

Thanks alot for the clarification

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources