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 STATUS which gives a more detailed error description:
我运行的SHOW ENGINE INNODB状态给出了更详细的错误描述:
------------------------
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 * 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!
我在*和其他网站上搜索了一下——我在这里看到了一篇有用的博客文章，里面有关于如何解决这个错误的建议——但是我不知道哪里出了问题。如有任何帮助，我们将不胜感激!
14 个解决方案
#1
35
You should make car_id a primary key in cars.
您应该将car_id作为汽车的主键。
#2
30
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).
注意:我遇到了同样的问题，因为引用的字段在两个不同的表中有不同的排序(它们有完全相同的类型)。
Make sure all your referenced fields have the same type AND the same collation!
确保所有引用的字段具有相同的类型和排序!
#3
13
Check that BOTH tables have the same ENGINE. For example if you have:
检查两个表是否有相同的引擎。例如，如果你有:
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.
如果您试图创建一个从表栏到表FOO的约束，它将不能在某些MySQL版本上工作。
Fix the issue by following:
解决问题的方法如下:
CREATE Table FOO () ENGINE=INNODB;
#4
10
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列为unsigned，以匹配引用的现有表，该表是“smallint unsigned”。有一个未签名和一个未签名导致MySQL无法在新表上创建外键。
id smallint(3) not null
does not match, for the sake of foreign keys,
不匹配，为了外键，
id smallint(3) unsigned not null
#5
8
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:
我的问题在我的comments表中，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之间的冲突。
#6
3
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.
固定它。
#7
3
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返回相同的错误。
#8
2
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”表中定义不为空和唯一的约束。
And moreover, both fields must be of the same type and collation.
而且，这两个字段必须具有相同的类型和排序。
#9
2
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 server max allowed packets being too low. To resolve this (on a Mac):
我还收到了这个错误(针对几个表)，以及约束错误和试图导入整个数据库(约800 MB)时连接和断开的MySQL。我的问题是MySQL服务器max允许数据包太低的结果。要解决这个问题(在Mac上):
- Opened /private/etc/my.conf
- 打开/私人/ etc / my.conf
- Under # The MySQL server, changed
max_allowed_packetfrom 1M to 4M (You may need to experiment with this value.) - 在# MySQL服务器下，将max_allowed_packet从1M更改为4M(您可能需要对这个值进行实验)。
- Restarted MySQL
- 重新启动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。
#10
1
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
检查以使您正在引用的字段与外键是完全匹配的，在我的例子中，一个是无签名的，另一个是签名的，所以我只是将它们更改为match，这是有效的
ALTER TABLE customer_information ADD CONSTRAINT fk_customer_information1 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
修改表customer_information添加约束fk_customer_information1外键(user_id)引用用户(id)在更新级联上删除级联。
#11
1
Solved:
解决:
Check to make sure Primary_Key and Foreign_Key are exact match with data types. If one is signed another one unsigned, it will be failed. Good practice is to make sure both are unsigned int.
检查以确保Primary_Key和Foreign_Key与数据类型完全匹配。如果一个签名是另一个未签名，它将失败。好的做法是确保两者都是无符号int。
#12
0
all, I solved a problem and wanted to share it:
总之，我解决了一个问题，并想与大家分享:
I had this error <> The issue was in that in my statement:
我有这个错误<>问题在我的声明中
alter table system_registro_de_modificacion add foreign key (usuariomodificador_id) REFERENCES Usuario(id) On delete restrict;
修改表system_registro_de_modificacion添加外键(usuariomodificador_id)引用Usuario(id)对删除的限制;
I had incorrectly written the CASING: it works in Windows WAMP, but in Linux MySQL it is more strict with the CASING, so writting "Usuario" instead of "usuario" (exact casing), generated the error, and was corrected simply changing the casing.
我写错了外壳:它在Windows WAMP中工作，但在Linux MySQL中，它对外壳要求更严格，所以写“Usuario”而不是“Usuario”(确切的外壳)，产生了错误，只需修改外壳即可。
#13
0
I was using a duplicate Foreign Key Name.
我使用的是重复的外键名。
Renaming the FK name solved my problem.
重命名FK名称解决了我的问题。
Clarification:
澄清:
Both tables had a constraint called PK1, FK1, etc. Renaming them/making the names unique solved the problem.
两个表都有一个名为PK1、FK1等的约束。重命名它们/使名称惟一解决了这个问题。
#14
0
The referenced column must be an index of a single column or the first column in multi column index, and the same type and the same collation.
引用的列必须是单个列的索引或多列索引中的第一列的索引，以及相同的类型和相同的排序。
My two tables have the different collations. It can be shown by issuing show table status like table_name and collation can be changed by issuing alter table table_name convert to character set utf8.
我的两张桌子有不同的排序。它可以通过像table_name这样的显示表状态来显示，可以通过将alter table table_name转换为字符集utf8来更改排序。
#1
35
You should make car_id a primary key in cars.
您应该将car_id作为汽车的主键。
#2
30
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).
注意:我遇到了同样的问题，因为引用的字段在两个不同的表中有不同的排序(它们有完全相同的类型)。
Make sure all your referenced fields have the same type AND the same collation!
确保所有引用的字段具有相同的类型和排序!
#3
13
Check that BOTH tables have the same ENGINE. For example if you have:
检查两个表是否有相同的引擎。例如，如果你有:
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.
如果您试图创建一个从表栏到表FOO的约束，它将不能在某些MySQL版本上工作。
Fix the issue by following:
解决问题的方法如下:
CREATE Table FOO () ENGINE=INNODB;
#4
10
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列为unsigned，以匹配引用的现有表，该表是“smallint unsigned”。有一个未签名和一个未签名导致MySQL无法在新表上创建外键。
id smallint(3) not null
does not match, for the sake of foreign keys,
不匹配，为了外键，
id smallint(3) unsigned not null
#5
8
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:
我的问题在我的comments表中，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之间的冲突。
#6
3
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.
固定它。
#7
3
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返回相同的错误。
#8
2
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”表中定义不为空和唯一的约束。
And moreover, both fields must be of the same type and collation.
而且，这两个字段必须具有相同的类型和排序。
#9
2
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 server max allowed packets being too low. To resolve this (on a Mac):
我还收到了这个错误(针对几个表)，以及约束错误和试图导入整个数据库(约800 MB)时连接和断开的MySQL。我的问题是MySQL服务器max允许数据包太低的结果。要解决这个问题(在Mac上):
- Opened /private/etc/my.conf
- 打开/私人/ etc / my.conf
- Under # The MySQL server, changed
max_allowed_packetfrom 1M to 4M (You may need to experiment with this value.) - 在# MySQL服务器下，将max_allowed_packet从1M更改为4M(您可能需要对这个值进行实验)。
- Restarted MySQL
- 重新启动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。
#10
1
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
检查以使您正在引用的字段与外键是完全匹配的，在我的例子中，一个是无签名的，另一个是签名的，所以我只是将它们更改为match，这是有效的
ALTER TABLE customer_information ADD CONSTRAINT fk_customer_information1 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
修改表customer_information添加约束fk_customer_information1外键(user_id)引用用户(id)在更新级联上删除级联。
#11
1
Solved:
解决:
Check to make sure Primary_Key and Foreign_Key are exact match with data types. If one is signed another one unsigned, it will be failed. Good practice is to make sure both are unsigned int.
检查以确保Primary_Key和Foreign_Key与数据类型完全匹配。如果一个签名是另一个未签名，它将失败。好的做法是确保两者都是无符号int。
#12
0
all, I solved a problem and wanted to share it:
总之，我解决了一个问题，并想与大家分享:
I had this error <> The issue was in that in my statement:
我有这个错误<>问题在我的声明中
alter table system_registro_de_modificacion add foreign key (usuariomodificador_id) REFERENCES Usuario(id) On delete restrict;
修改表system_registro_de_modificacion添加外键(usuariomodificador_id)引用Usuario(id)对删除的限制;
I had incorrectly written the CASING: it works in Windows WAMP, but in Linux MySQL it is more strict with the CASING, so writting "Usuario" instead of "usuario" (exact casing), generated the error, and was corrected simply changing the casing.
我写错了外壳:它在Windows WAMP中工作，但在Linux MySQL中，它对外壳要求更严格，所以写“Usuario”而不是“Usuario”(确切的外壳)，产生了错误，只需修改外壳即可。
#13
0
I was using a duplicate Foreign Key Name.
我使用的是重复的外键名。
Renaming the FK name solved my problem.
重命名FK名称解决了我的问题。
Clarification:
澄清:
Both tables had a constraint called PK1, FK1, etc. Renaming them/making the names unique solved the problem.
两个表都有一个名为PK1、FK1等的约束。重命名它们/使名称惟一解决了这个问题。
#14
0
The referenced column must be an index of a single column or the first column in multi column index, and the same type and the same collation.
引用的列必须是单个列的索引或多列索引中的第一列的索引，以及相同的类型和相同的排序。
My two tables have the different collations. It can be shown by issuing show table status like table_name and collation can be changed by issuing alter table table_name convert to character set utf8.
我的两张桌子有不同的排序。它可以通过像table_name这样的显示表状态来显示，可以通过将alter table table_name转换为字符集utf8来更改排序。