CODEWITHSUNDEEP
mysql
Joey
Joey

Reputation: 45

Why do I keep getting error 1005: Can't create table (errno: 150)?

I created the following tables in the mentioned order. But I get an error when I create the enrolls table.

I know it has to do with the foreign key constraint. But I feel I have done everything right. I don't know what I'm doing wrong:

create table Course (
    CourseNo integer(10),
    CourseName varchar(50),
    Department varchar(20),
    primary key (CourseNo)) Engine = InnoDB;

create table Section (
    CourseNo integer(10),
    SectionNo integer(10),
    Instructor varchar(50),
    primary key (CourseNo, SectionNo),
    foreign key (CourseNo) references Course(CourseNo)) Engine = InnoDB;

create table Student (
    SSN varchar(9),
    FirstName char(50),
    LastName char(50),
    Street char(50),
    City char(50),
    State char(50),
    Zip char(5),
    primary key (SSN)) Engine = InnoDB;

create table Enrolls (
    SSN varchar(9),
    SectionNo integer(10),
    CourseNo integer(10),
    foreign key (SectionNo) references Section(SectionNo),
    foreign key (CourseNo) references Section(CourseNo),
    foreign key (SSN) references Student(SSN),
    primary key (SSN, SectionNo, CourseNo)) Engine = InnoDB;

Upvotes: 0

Views: 50

Answers (1)

Rahul
Rahul

Reputation: 77866

No, you have done wrong while defining foreign key. The below one's in exact

foreign key (SectionNo) references Section(SectionNo)
foreign key (CourseNo) references Section(CourseNo),

In your Section table you have two columns defined as primary key primary key (CourseNo, SectionNo). So in your FK you must reference both of them with two fields/column like

foreign key (CourseNo, SectionNo) references Section(CourseNo, SectionNo)

Which thing means, your last table must be created this way:

create table Enrolls (
    SSN varchar(9),
    SectionNo integer(10),
    CourseNo integer(10),
    primary key (SSN, SectionNo, CourseNo),
    foreign key (CourseNo, SectionNo) references Section(CourseNo, SectionNo)
    ) Engine = InnoDB;

See a demo fiddle here http://sqlfiddle.com/#!2/741b2

Upvotes: 1

Related Questions