MySQL“错误 1005 (HY000): 无法创建表 'foo.#sql-12c_4' (errno: 150)”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2799021/
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 "ERROR 1005 (HY000): Can't create table 'foo.#sql-12c_4' (errno: 150)"
提问by Ankur Banerjee
I was working on creating some tables in database foo, but every time I end up with errno 150 regarding the foreign key. Firstly, here's my code for creating tables:
我正在努力在数据库中创建一些表foo，但每次我最终都会得到关于外键的 errno 150。首先，这是我创建表的代码：
CREATE TABLE Clients
(
client_id CHAR(10) NOT NULL ,
client_name CHAR(50) NOT NULL ,
provisional_license_num CHAR(50) NOT NULL ,
client_address CHAR(50) NULL ,
client_city CHAR(50) NULL ,
client_county CHAR(50) NULL ,
client_zip CHAR(10) NULL ,
client_phone INT NULL ,
client_email CHAR(255) NULL ,
client_dob DATETIME NULL ,
test_attempts INT NULL
);
CREATE TABLE Applications
(
application_id CHAR(10) NOT NULL ,
office_id INT NOT NULL ,
client_id CHAR(10) NOT NULL ,
instructor_id CHAR(10) NOT NULL ,
car_id CHAR(10) NOT NULL ,
application_date DATETIME NULL
);
CREATE TABLE Instructors
(
instructor_id CHAR(10) NOT NULL ,
office_id INT NOT NULL ,
instructor_name CHAR(50) NOT NULL ,
instructor_address CHAR(50) NULL ,
instructor_city CHAR(50) NULL ,
instructor_county CHAR(50) NULL ,
instructor_zip CHAR(10) NULL ,
instructor_phone INT NULL ,
instructor_email CHAR(255) NULL ,
instructor_dob DATETIME NULL ,
lessons_given INT NULL
);
CREATE TABLE Cars
(
car_id CHAR(10) NOT NULL ,
office_id INT NOT NULL ,
engine_serial_num CHAR(10) NULL ,
registration_num CHAR(10) NULL ,
car_make CHAR(50) NULL ,
car_model CHAR(50) NULL
);
CREATE TABLE Offices
(
office_id INT NOT NULL ,
office_address CHAR(50) NULL ,
office_city CHAR(50) NULL ,
office_County CHAR(50) NULL ,
office_zip CHAR(10) NULL ,
office_phone INT NULL ,
office_email CHAR(255) NULL
);
CREATE TABLE Lessons
(
lesson_num INT NOT NULL ,
client_id CHAR(10) NOT NULL ,
date DATETIME NOT NULL ,
time DATETIME NOT NULL ,
milegage_used DECIMAL(5, 2) NULL ,
progress CHAR(50) NULL
);
CREATE TABLE DrivingTests
(
test_num INT NOT NULL ,
client_id CHAR(10) NOT NULL ,
test_date DATETIME NOT NULL ,
seat_num INT NOT NULL ,
score INT NULL ,
test_notes CHAR(255) NULL
);
ALTER TABLE Clients ADD PRIMARY KEY (client_id);
ALTER TABLE Applications ADD PRIMARY KEY (application_id);
ALTER TABLE Instructors ADD PRIMARY KEY (instructor_id);
ALTER TABLE Offices ADD PRIMARY KEY (office_id);
ALTER TABLE Lessons ADD PRIMARY KEY (lesson_num);
ALTER TABLE DrivingTests ADD PRIMARY KEY (test_num);
ALTER TABLE Applications ADD CONSTRAINT FK_Applications_Offices FOREIGN KEY (office_id) REFERENCES Offices (office_id);
ALTER TABLE Applications ADD CONSTRAINT FK_Applications_Clients FOREIGN KEY (client_id) REFERENCES Clients (client_id);
ALTER TABLE Applications ADD CONSTRAINT FK_Applications_Instructors FOREIGN KEY (instructor_id) REFERENCES Instructors (instructor_id);
ALTER TABLE Applications ADD CONSTRAINT FK_Applications_Cars FOREIGN KEY (car_id) REFERENCES Cars (car_id);
ALTER TABLE Lessons ADD CONSTRAINT FK_Lessons_Clients FOREIGN KEY (client_id) REFERENCES Clients (client_id);
ALTER TABLE Cars ADD CONSTRAINT FK_Cars_Offices FOREIGN KEY (office_id) REFERENCES Offices (office_id);
ALTER TABLE Clients ADD CONSTRAINT FK_DrivingTests_Clients FOREIGN KEY (client_id) REFERENCES Clients (client_id);
These are the errors that I get:
这些是我得到的错误：
mysql> ALTER TABLE Applications ADD CONSTRAINT FK_Applications_Cars FOREIGN KEY
(car_id) REFERENCES Cars (car_id);
ERROR 1005 (HY000): Can't create table 'foo.#sql-12c_4' (errno: 150)
I ran SHOW ENGINE INNODB STATUSwhich gives a more detailed error description:
我跑了SHOW ENGINE INNODB STATUS它给出了更详细的错误描述：
------------------------
LATEST FOREIGN KEY ERROR
------------------------
100509 20:59:49 Error in foreign key constraint of table foo/#sql-12c_4:
FOREIGN KEY (car_id) REFERENCES Cars (car_id):
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html
for correct foreign key definition.
------------
I searched around on StackOverflow and elsewhere online - came across a helpful blog post here with pointers on how to resolve this error- but I can't figure out what's going wrong. Any help would be appreciated!
我在 StackOverflow 和在线其他地方进行了搜索 -在这里看到了一篇有用的博客文章，其中包含有关如何解决此错误的提示- 但我无法弄清楚出了什么问题。任何帮助，将不胜感激！
采纳答案by Paul Tomblin
You should make car_id a primary key in cars.
你应该让 car_id 成为汽车的主键。
回答by NickT
Note: I had the same problem, and it was because the referenced field was in a different collation in the 2 different tables (they had exact same type).
注意：我遇到了同样的问题，这是因为引用的字段在 2 个不同的表中处于不同的排序规则中（它们具有完全相同的类型）。
Make sure all your referenced fields have the same type AND the same collation!
确保所有引用的字段都具有相同的类型和相同的排序规则！
回答by Ville Rontti
Check that BOTH tables have the same ENGINE. For example if you have:
检查两个表是否具有相同的 ENGINE。例如，如果您有：
CREATE Table FOO ();
and:
和：
CREATE Table BAR () ENGINE=INNODB;
If you try to create a constraint from table BAR to table FOO, it will not work on certain MySQL versions.
如果您尝试创建从表 BAR 到表 FOO 的约束，它将不适用于某些 MySQL 版本。
Fix the issue by following:
通过以下方式修复问题：
CREATE Table FOO () ENGINE=INNODB;
回答by Eric L.
Subtle, but this error got me because I forgot to declare a smallint column as unsigned to match the referenced, existing table which was "smallint unsigned." Having one unsigned and one not unsigned caused MySQL to prevent the foreign key from being created on the new table.
微妙，但这个错误让我感到困惑，因为我忘记将 smallint 列声明为无符号以匹配引用的现有表，即“smallint unsigned”。一个未签名和一个未签名导致 MySQL 阻止在新表上创建外键。
id smallint(3) not null
does not match, for the sake of foreign keys,
不匹配，为了外键，
id smallint(3) unsigned not null
回答by Mars Redwyne
I got this completely worthless and uninformative error when I tried to:
当我尝试执行以下操作时，我得到了这个完全没有价值且没有信息的错误：
ALTER TABLE `comments` ADD CONSTRAINT FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;
My problem was in my comments table, user_id was defined as:
我的问题是在我的评论表中，user_id 被定义为：
`user_id` int(10) unsigned NOT NULL
So... in my case, the problem was with the conflict between NOT NULL, and ON DELETE SET NULL.
所以......在我的例子中，问题在于 NOT NULL 和 ON DELETE SET NULL 之间的冲突。
回答by user2315570
Also both the tables need to have same character set.
此外，两个表都需要具有相同的字符集。
for e.g.
例如
CREATE TABLE1 (
FIELD1 VARCHAR(100) NOT NULL PRIMARY KEY,
FIELD2 VARCHAR(100) NOT NULL
)ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_bin;
to
到
CREATE TABLE2 (
Field3 varchar(64) NOT NULL PRIMARY KEY,
Field4 varchar(64) NOT NULL,
CONSTRAINT FORIGEN KEY (Field3) REFERENCES TABLE1(FIELD1)
) ENGINE=InnoDB;
Will fail because they have different charsets. This is another subtle failure where mysql returns same error.
会失败，因为它们有不同的字符集。这是另一个微妙的失败，其中 mysql 返回相同的错误。
回答by Jeshurun
I use Ubuntu linux, and in my case the error was caused by incorrect statement syntax (which I found out by typing perror 150 at the terminal, which gives
我使用 Ubuntu linux，在我的情况下，错误是由不正确的语句语法引起的（我是通过在终端输入 perror 150 发现的，它给出了
MySQL error code 150: Foreign key constraint is incorrectly formed
MySQL 错误代码 150：外键约束的格式不正确
Changing the syntax of the query from
将查询的语法从
alter table scale add constraint foreign key (year_id) references year.id;
to
到
alter table scale add constraint foreign key (year_id) references year(id);
fixed it.
修复。
回答by AKSinha
The referenced field must be a "Key" in the referenced table, not necessarily a primary key. So the "car_id" should either be a primary key or be defined with NOT NULL and UNIQUE constraints in the "Cars" table.
被引用字段必须是被引用表中的“键”，不一定是主键。因此，“car_id”应该是主键，或者在“Cars”表中定义为 NOT NULL 和 UNIQUE 约束。
And moreover, both fields must be of the same type and collation.
此外，两个字段必须具有相同的类型和排序规则。
回答by h-bomb
I also received this error (for several tables) along with constraint errors and MySQL connecting and disconnecting when attempting to import an entire database (~800 MB). My issue was the result of The MySQL servermax allowed packets being too low. To resolve this (on a Mac):
在尝试导入整个数据库 (~800 MB) 时，我还收到了此错误（针对多个表）以及约束错误和 MySQL 连接和断开连接。我的问题是MySQL 服务器最大允许数据包太低的结果。要解决此问题（在 Mac 上）：
- Opened /private/etc/my.conf
- Under # The MySQL server, changed
max_allowed_packetfrom 1M to 4M (You may need to experiment with this value.) - Restarted MySQL
- 打开/private/etc/my.conf
- 在#The MySQL server 下，
max_allowed_packet从 1M更改为 4M（您可能需要尝试使用此值。） - 重启 MySQL
The database imported successfully after that.
之后数据库导入成功。
Note I am running MySQL 5.5.12 for Mac OS X (x86 64 bit).
注意我正在为 Mac OS X（x86 64 位）运行 MySQL 5.5.12。
回答by Ashahmali
check to make the field you are referencing to is an exact match with foreign key, in my case one was unsigned and the other was signed so i just changed them to match and this worked
检查以确保您引用的字段与外键完全匹配，在我的情况下，一个是未签名的，另一个是签名的，所以我只是将它们更改为匹配，这有效
ALTER TABLE customer_information
ADD CONSTRAINT fk_customer_information1
FOREIGN KEY (user_id)
REFERENCES users(id)
ON DELETE CASCADE
ON UPDATE CASCADE
ALTER TABLE customer_information ADD CONSTRAINT fk_customer_information1 FOREIGN KEY ( user_id) REFERENCES users( id) ON DELETE CASCADE ON UPDATE CASCADE
