CODEWITHSUNDEEP
mysqlsqlforeign-keysforeign-key-relationship
tbenz9
tbenz9

Reputation: 446

MYSQL Foreign Key errno: 150 cannot create tables

I've been reading around StackOverflow and various forums about this problem but I cannot seem to figure it out. When trying to run the "CREATE TABLE Class" and "CREATE TABLE Enroll" commands below I get "ERROR 1005: Can't create table university.class (errno: 150)". I am using InnoDB as my storage engine. The first two "CREATE" statements work fine.

What changes do I need to make so that the "CREATE TABLE Class and CREATE TABLE Enroll" sections work?

CREATE TABLE Student (
    stuId VARCHAR(6),
    lastName VARCHAR(20) NOT NULL,
    firstName VARCHAR(20) NOT NULL,
    major VARCHAR(10),
    credits FLOAT(3) DEFAULT 0,
    CONSTRAINT Student_stuId_pk PRIMARY KEY (stuId),
    CONSTRAINT Student_credits_cc CHECK ((credits>=0) AND (credits < 150)));

CREATE TABLE Faculty (
    facId VARCHAR(6),
    name VARCHAR(20) NOT NULL,
    department VARCHAR(20),
    rank VARCHAR(10),
    CONSTRAINT Faculty_facId_pk PRIMARY KEY (facId));

CREATE TABLE Class (
    classNumber VARCHAR(8),
    facId VARCHAR(6) NOT NULL,
    schedule VARCHAR(8),
    room VARCHAR(6),
    CONSTRAINT Class_classNumber_pk PRIMARY KEY (classNumber),
    CONSTRAINT Class_facId_fk FOREIGN KEY (facId) REFERENCES Faculty
        (facId) ON DELETE SET NULL,
    CONSTRAINT Class_schedule_room_uk UNIQUE (schedule, room));

CREATE TABLE Enroll (
    stuId VARCHAR(6),
    classNumber VARCHAR(8),
    grade VARCHAR(2),
    CONSTRAINT Enroll_classNumber_stuId_pk PRIMARY KEY
        (classNumber, stuId),
    CONSTRAINT Enroll_classNumber_fk FOREIGN KEY (classNumber)
        REFERENCES Class (classNumber) ON DELETE CASCADE,
CONSTRAINT Enroll_stuId_fk FOREIGN KEY (stuId) REFERENCES Student
(stuId)ON DELETE CASCADE);

Here is the full command and error:

mysql> CREATE TABLE Class (classNumber VARCHAR(8), facId VARCHAR(6) NOT NULL, schedule VARCHAR(8), room VARCHAR(6), CONSTRAINT Class_classNumber_pk PRIMARY KEY (classNumber), CONSTRAINT Class_facId_fk FOREIGN KEY (facId) REFERENCES Faculty (facId) ON DELETE SET NULL, CONSTRAINT Class_schedule_room_uk UNIQUE (schedule, room));
ERROR 1005 (HY000): Can't create table 'university.Class' (errno: 150)

Upvotes: 1

Views: 1500

Answers (3)

Hung Hoang
Hung Hoang

Reputation: 717

Remove NOT NULL in defination of facId

Upvotes: 2

Chamal
Chamal

Reputation: 1449

This is occur due to a foreign keye error. To get more details on foreign key error, run SHOW ENGINE INNODB STATUS\G and look at the "LATEST FOREIGN KEY ERROR" section.

I think it will tell that the foreign key is invalid because there is no unique index or primary key index on Faculty.facid.

Upvotes: 0

Gaurav Singla
Gaurav Singla

Reputation: 1451

Did you created the Faculty Table first. check this link

inno db error

which says if you get 1005 with error 150 this means

a foreign key constraint was not correctly formed

Upvotes: 0

Related Questions