MySQL 外键约束因“在 [表] 上创建外键时出错（检查数据类型）”而失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11926324/
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
Foreign Key Constrain Fails with "Error creating foreign key on [table] (check data types)"
提问by Dzhuneyt
The following query fails with error "Error creating foreign key on city (check data types)":
以下查询失败并显示错误“在城市上创建外键时出错（检查数据类型）”：
ALTER TABLE `hotels` ADD FOREIGN KEY ( `city` )
REFERENCES `mydatabase`.`cities` (`id`)
ON DELETE CASCADE ON UPDATE CASCADE ;
Basically I want to have a ony-to-many relation between city.id and hotels.city.
基本上我想在city.id 和hotels.city 之间建立一对多的关系。
Here are both tables:
这是两个表：
CREATE TABLE IF NOT EXISTS `cities` (
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS `hotels` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`city` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `city` (`city`)
) ENGINE=InnoDB;
回答by Mihai Stancu
The data types need to match:
数据类型需要匹配：
cities.id int(11)
hotels.city bigint(20)
Needs to become either:
需要成为：
cities.id bigint(20)
hotels.city bigint(20)
Or:
或者：
cities.id int(11)
hotels.city int(11)
Depending on what your application needs.
取决于您的应用程序需要什么。
Also worth mentioning is the fact that both need to be either signed or unsigned.
另外值得一提的是，两者都需要签名或未签名。
You may need to OPTIMIZEyour tables after changing the data types to match.
OPTIMIZE在更改数据类型以匹配后，您可能需要使用您的表。
回答by Roy Lenferink
I know this is quite an old thread, but I spent some time with this error as well.
我知道这是一个很旧的线程，但我也花了一些时间来解决这个错误。
The situation I had was the following one:
我遇到的情况如下：
Table 1: administrations (Primary key: AdministrationId)
Table 2: invoices (Foreign key to AdministrationId)
Table 3: users (error pops up while creating foreign key)
Table 1: administrations (Primary key: AdministrationId)
Table 2: invoices (Foreign key to AdministrationId)
Table 3: users (error pops up while creating foreign key)
The colomns AdministrationIdin my invoicesand userstable were both of the same type as the AdministrationIdcolumn in the administrationstable.
该colomnsAdministrationId在我invoices和users表都是同一类型的的AdministrationId列administrations表。
The error on my side was that I tried to create a foreign key called administration_idin my userstable. But a minute before that I already created a foreign key in my invoicestable also called administration_id. When I tried to give the foreign key another name, it worked out fine.
我这边的错误是我试图administration_id在我的users表中创建一个外键。但在那之前一分钟，我已经在我的invoices表中创建了一个外键，也称为administration_id. 当我尝试给外键起另一个名字时，结果很好。
Therefore, keep in mind to correctly name your foreign keys (e.g. prefix them with the table name, eg: invoices_administration_idand users_administration_id). Multiple foreign keys with the same name may not exist (within the same database).
因此，请记住正确命名您的外键（例如，在它们前面加上表名，例如:invoices_administration_id和users_administration_id）。多个同名的外键可能不存在（在同一个数据库中）。
回答by Frédéric
Worth mentioning, but the collation should be the same between both table
值得一提，但两个表之间的排序规则应该相同
I faced the same issue with varchar(64) fields in both tables, and It took me some time to identify the problem was coming from the collation field which was not the same between the 2 table fields.
我在两个表中的 varchar(64) 字段都遇到了同样的问题，我花了一些时间来确定问题来自于 2 个表字段之间不相同的排序规则字段。
回答by Clement Smith
I know this has been answered and I know this question is old. However, I just came across this same error with a different cause and, since this is the top result for this error, I thought I would put this information here both for my own use in the future as well as anyone else who happens along after me.
我知道这已经得到回答，我知道这个问题很老了。但是，我只是遇到了具有不同原因的相同错误，并且由于这是此错误的最高结果，因此我想我会将这些信息放在这里供我将来自己使用，以及之后发生的任何其他人使用我。
My columns were both bigint and unsigned. However, after first creating the referenced tables, I then went on to change the name of the primary key column. Nothing else about it had changed, but I was unable to create a foreign key relationship. I ended up dropping the referenced tables and recreating them using the column names I desired and I was able to create the foreign key relationships.
我的列都是 bigint 和 unsigned。但是，在首先创建引用表之后，我继续更改主键列的名称。关于它的任何其他内容都没有改变，但我无法创建外键关系。我最终删除了引用的表并使用我想要的列名重新创建它们，并且我能够创建外键关系。
回答by Nagibaba
Updating hotels.cityto unsignedworked for me. Because cities.idis unsigned
更新hotels.city到unsigned为我工作。因为cities.id是unsigned
