MySQL Errno 150

Question

I'm creating a few simple tables and I can't get passed this foreign key error and I'm not sure why. Here's the script below.

create TABLE Instructors (

ID varchar(10),
First_Name varchar(50) NOT NULL,
Last_Name varchar(50) NOT NULL,
PRIMARY KEY (ID)
);

create table Courses (

Course_Code varchar(10),
Title varchar(50) NOT NULL,
PRIMARY KEY (Course_Code)

);


create table Sections (
Index_No int,
Course_Code varchar(10),
Instructor_ID varchar(10),
PRIMARY KEY (Index_No),
FOREIGN KEY (Course_Code) REFERENCES Courses(Course_Code)
    ON DELETE cascade
    ON UPDATE cascade,
FOREIGN KEY (Instructor_ID) REFERENCES Instructors(ID)
    ON DELETE set default

);

Error Code: 1005. Can't create table '336_project.sections' (errno: 150)

My data types seem identical and the syntax seems correct. Can anyone point out what I'm not seeing here?

I'm using MySQL Workbench 5.2

Answer 1

This error also occurs if you are relating columns of different types, eg. int in the source table and BigInt in the destination table.

Answer 2

If you're using the InnoDB engine, the ON DELETE SET DEFAULT is your problem. Here's an excerpt from the manual:

While SET DEFAULT is allowed by the MySQL Server, it is rejected as invalid by InnoDB. CREATE TABLE and ALTER TABLE statements using this clause are not allowed for InnoDB tables.

You can use ON DELETE CASCADE or ON DELETE SET NULL, but not ON DELETE SET DEFAULT. There's more information here.

Answer 3

You can run

SHOW ENGINE INNODB STATUS

to read the reason of the failure in a human readable format

e.g.

------------------------
LATEST FOREIGN KEY ERROR
------------------------
150331 15:51:01 Error in foreign key constraint of table foobar/#sql-413_81:
FOREIGN KEY (`user_id`) REFERENCES `foobar`.`users`(`id`) ON DELETE SET NULL ON UPDATE CASCADE:
You have defined a SET NULL condition though some of the columns are defined as NOT NULL.

Answer 4

In order to create a FOREIGN KEY with reference to another table, the keys from both tables should be PRIMARY KEY and with the same datatype.

In your table sections, PRIMARY KEY is of different datatype i.e INT but in another table, it's of type i.e VARCHAR.