May 31, 2013 at 1:03 pm
Hi
I have done the script below but when I execute it on sql server 2008 an error comes up. I'm having hard time to figure it out. Please any help would be very appreciate. Thank you
Here is the script:
use master
go
create database SouthCommunityCollege
go
use SouthCommunityCollege
go
create table Semester
(semesterID int primary key, semesterName varchar (30))
create table Section
(sectionID int primary key, sectionName varchar (30))
create table Course
(courseID int primary key, sectionID int, semesterID int,
courseTitle varchar (30), courseCredits varchar (30),
foreign key (semesterID) references Semester (semesterID),
foreign key (sectionID) references Section (sectionID))
create table Student
(studentID int primary key, studentName varchar (30),
studentAddress varchar (50), studentPhone int,
studentEmail varchar (20))
create table StudentAccount
(studentID int, sectionID int,
courseID int, instructorID int, grades varchar (30),
constraint pk_StudentAccount primary key (studentID, sectionID),
foreign key (courseID) references Course (courseID),
foreign key (instructorID) references Instructor (instructorID))
create table Instructor
(instructorID int primary key, instructorName varchar (30),
instructorPhone int, instructorOffice varchar (30), instructorEmail varchar (20))
create table InstructorCourse
(instructorID int primary key, courseID int)
Here is the error
Msg 1767, Level 16, State 0, Line 19
Foreign key 'FK__StudentAc__instr__117F9D94' references invalid table 'Instructor'.
Msg 1750, Level 16, State 0, Line 19
Could not create constraint. See previous errors.
May 31, 2013 at 1:08 pm
it's the order of operations.
create table StudentAccount references table Instructor,
so the script has to create table Instructor before it can
create table StudentAccount;
i reordered that one table here, and it works correctly.
great job on explaining and showing us what you were doing!
create database SouthCommunityCollege
go
use SouthCommunityCollege
go
create table Semester
(semesterID int primary key, semesterName varchar (30))
create table Section
(sectionID int primary key, sectionName varchar (30))
create table Course
(courseID int primary key, sectionID int, semesterID int,
courseTitle varchar (30), courseCredits varchar (30),
foreign key (semesterID) references Semester (semesterID),
foreign key (sectionID) references Section (sectionID))
create table Student
(studentID int primary key, studentName varchar (30),
studentAddress varchar (50), studentPhone int,
studentEmail varchar (20))
create table Instructor
(instructorID int primary key, instructorName varchar (30),
instructorPhone int, instructorOffice varchar (30), instructorEmail varchar (20))
create table StudentAccount
(studentID int, sectionID int,
courseID int, instructorID int, grades varchar (30),
constraint pk_StudentAccount primary key (studentID, sectionID),
foreign key (courseID) references Course (courseID),
foreign key (instructorID) references Instructor (instructorID))
create table InstructorCourse
(instructorID int primary key, courseID int)
Lowell
May 31, 2013 at 1:09 pm
The easiest way is to change the order of the tables creation put StudentAccount after Instructor.
June 1, 2013 at 5:56 am
Thank you, haven't seen that mistake!!!!! it works just fine but when i do database diagram on sql, there is no relation between student table, studentAccount and studentAccount to instructor,section,course tables. Thanks again for your help, i really do appreciate it.:-)
June 1, 2013 at 7:45 am
Just change the order of the table creation as per suggested by the experts
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy