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

score 35 · Answer 1 · answered Dec 04 '13 at 15:12

35

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

answered Dec 04 '13 at 15:12

JohnL

13,682
4
19
23

3

or the referenced table has not InnoDB engine – Gavriel May 12 '14 at 14:41
This was my issue. I am using Laravel and my parent column was an "increments" column. I had to change my reference column to an unsignedInteger. – amlane86 Jun 21 '18 at 15:12

Ed Gibbs · Accepted Answer · 2013-04-26T01:46:44.447

24

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.

edited Apr 26 '13 at 01:46

answered Apr 26 '13 at 01:28

Ed Gibbs

25,924
4
46
69

Hi, thanks for your quick response. I tried changing to SET NULL and it still doesn't work. I even removed all of the ON UPDATE/DELETE lines and still gives me the error. Should I be specifying a particular engine to use? – Sixers17 Apr 26 '13 at 01:54
1

Disregard the last comment, I fixed it. You were right, I had to change the default to null, but also I had create indexes for each foreign key reference. Once i did that, it worked! – Sixers17 Apr 26 '13 at 02:16
@Sixers17 - just so you know, when I tried your `CREATE TABLE` statements here, the only thing I had to do to make them work was the change to `ON DELETE SET NULL`. I didn't have to add indexes. That said, it's a good idea to have indexes along your FKs anyway so all's well :) – Ed Gibbs Apr 26 '13 at 02:24
Hm interesting, I wonder why mine doesn't work without indexes. – Sixers17 Apr 26 '13 at 02:40
1

I notice you can't set the column to `NOT NULL` either. Discovered migrating an `ON DELETE RESTRICT` column to an `ON DELETE SET NULL` column. – quickshiftin Dec 15 '14 at 05:36

score 20 · Answer 3 · answered Mar 31 '15 at 16:01

20

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.

answered Mar 31 '15 at 16:01

Riccardo Galli

12,419
6
64
62

2

This is treasure! This helps diagnose other issue as well. – mahesh nanayakkara Mar 22 '18 at 10:18

score 4 · Answer 4 · edited Oct 19 '17 at 04:05

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.

edited Oct 19 '17 at 04:05

wscourge

10,657
14
59
80

answered Oct 19 '17 at 03:55

Mayur Mahajan

122
11

score 3 · Answer 5 · answered Nov 25 '13 at 11:05

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.

score 1 · Answer 6 · answered Apr 26 '13 at 01:27

1

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 ?

answered Apr 26 '13 at 01:27

Ian Kenney

6,376
1
25
44

Should I be putting Engine=InnoDB after table declarations? – Sixers17 Apr 26 '13 at 01:55

score 1 · Answer 7 · answered Feb 26 '16 at 17:07

1

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;

answered Feb 26 '16 at 17:07

Duco

721
7
11

Sudarshani Perera · Answer 8 · 2015-01-24T05:21:30.783

0

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.

edited Jan 24 '15 at 05:21

answered Jan 23 '15 at 05:05

Sudarshani Perera

238
2
3
14

score 0 · Answer 9 · answered Nov 30 '15 at 23:21

0

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

answered Nov 30 '15 at 23:21

Kadir Erturk

583
6
8

MySQL Errno 150

9 Answers9

Linked

Related