need help on sql script

  • 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.

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • The easiest way is to change the order of the tables creation put StudentAccount after Instructor.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • 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.:-)

  • 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