MySQL Errno 150

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

Answers


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.


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


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.

It may also be the case if you are not specifying the ON DELETE at all but are trying to reference a MYISAM table from InnoDB table:

CREATE TABLE `table1`(
    `id` INT UNSIGNED NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=MYISAM CHARACTER SET UTF8;

CREATE TABLE `table2`(
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `table1_id` INT UNSIGNED NOT NULL,
    `some_value` VARCHAR(255) NOT NULL,


    PRIMARY KEY (`id`),
    KEY `fk_table1_id`(`table1_id`),

    CONSTRAINT FOREIGN KEY (`table1_id`) REFERENCES `table1`(`id`)
) ENGINE=INNODB CHARACTER SET UTF8;

The above will throw errno 150. One need to change the first table to InnoDB too for this to work.


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.


It is failing on the

ON DELETE set default 

I have not come across that before and I am not seeing it in the manuals either ( but then it is late )

Update

just seen this in 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.

I guess you may be using InnoDB tables ?


Here Problem is in database engine ( table1 MYISAM and table2 ENGINE). To set FOREIGN KEYs,

  • Both table must be in same ENGINE and same charset.
  • PK column in parent and FK column must be in same data type and same collation type.

Hope you got an idea.


Make sure that table type is InnoDB, MyISAM does not support foreign key, afaik.


For completeness sake - you will also get this error if you make a foreign reference to a table that isn't defined at the time;


Need Your Help

In LINQ-SQL, wrap the DataContext is an using statement - pros cons

linq linq-to-sql datacontext using

Can someone pitch in their opinion about pros/cons between wrapping the DataContext in an using statement or not in LINQ-SQL in terms of factors as performance, memory usage, ease of coding, right ...