MySQL 错误 150
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16227199/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Errno 150
提问by Sixers17
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)
错误代码：1005。无法创建表“336_project.sections”（错误号：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
我正在使用 MySQL Workbench 5.2
回答by JohnL
This error also occurs if you are relating columns of different types, eg. int in the source table and BigInt in the destination table.
如果您关联不同类型的列，也会发生此错误，例如。int 在源表中，BigInt 在目标表中。
回答by Ed Gibbs
If you're using the InnoDB engine, the ON DELETE SET DEFAULTis your problem. Here's an excerpt from the manual:
如果您使用的是 InnoDB 引擎，这ON DELETE SET DEFAULT就是您的问题。这是手册的摘录：
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.
虽然 SET DEFAULT 被 MySQL 服务器允许，但它被 InnoDB 拒绝为无效。InnoDB 表不允许使用此子句的 CREATE TABLE 和 ALTER TABLE 语句。
You can use ON DELETE CASCADEor ON DELETE SET NULL, but not ON DELETE SET DEFAULT. There's more information here.
您可以使用ON DELETE CASCADE或ON DELETE SET NULL，但不能使用ON DELETE SET DEFAULT。还有更多的信息在这里。
回答by Riccardo Galli
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.
回答by Shimon Rachlenko
It may also be the case if you are not specifying the ON DELETEat all but are trying to reference a MYISAM table from InnoDB table:
如果您根本没有指定ON DELETE，而是尝试从 InnoDB 表中引用 MYISAM 表，也可能是这种情况：
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.
以上将抛出errno 150。还需要将第一个表更改为 InnoDB 才能正常工作。
回答by Mayur Mahajan
In order to create a FOREIGN KEYwith reference to another table, the keys from both tables should be PRIMARY KEYand with the same datatype.
为了创建FOREIGN KEY对另一个表的引用，两个表中的键应该PRIMARY KEY具有相同的数据类型。
In your table sections, PRIMARY KEYis of different datatype i.e INTbut in another table, it's of type i.e VARCHAR.
在您的表部分中，PRIMARY KEY是不同的数据类型，即，INT但在另一个表中，它的类型是 ie VARCHAR。
回答by Duco
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;
为了完整起见 - 如果您对当时未定义的表进行外部引用，您也会收到此错误；
回答by Ian Kenney
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.
虽然 SET DEFAULT 被 MySQL 服务器允许，但它被 InnoDB 拒绝为无效。InnoDB 表不允许使用此子句的 CREATE TABLE 和 ALTER TABLE 语句。
I guess you may be using InnoDB tables ?
我猜您可能正在使用 InnoDB 表？
回答by Kadir Erturk
Make sure that table type is InnoDB, MyISAM does not support foreign key, afaik.
确保表类型是 InnoDB，MyISAM 不支持外键，afaik。
回答by Sudarshani Perera
Here Problem is in database engine ( table1 MYISAMand table2 ENGINE).
To set FOREIGN KEYs,
这里问题出在数据库引擎（ table1MYISAM和 table2 ENGINE）中。要设置外键，
- 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.
- 两个表必须在相同的 ENGINE 和相同的字符集中。
- parent 中的 PK 列和 FK 列必须是相同的数据类型和相同的排序规则类型。
Hope you got an idea.
希望你有想法。
