MySQL：错误代码：1215.无法添加外键约束 [英] MySQL : Error Code: 1215. Cannot add foreign key constraint

问题描述

我有两个表：

pre $ $ $ $ $ b $ user $ id $ var $ PK
密码varchar（255）

表：agenzie
列：
agenziaId varchar（255）PK
userId varchar（255）

当我尝试在agenzie上创建外键时，会显示以下错误消息：

  ALTER TABLE agenzie ADD FOREIGN KEY（userId）REFERENCES utenti（userId）错误代码：1215.无法添加外键约束0.015秒
  
 
 
 
谢谢。
 
 
 
编辑：
  DROP TABLE如果存在`utenti`; 
 / *！40101 SET @saved_cs_client = @@ character_set_client * /; 
 / *！40101 SET character_set_client = utf8 * /; 
 CREATE TABLE`
`userId`varchar（255）CHARACTER SET latin1 NOT NULL，
`password` varchar（255）CHARACTER SET latin1 NOT NULL，
 PRIMARY KEY （`userId`）
）ENGINE = InnoDB DEFAULT CHARSET = utf8; 
 
删除表如果存在`agenzie`; 
 / *！40101 SET @saved_cs_client = @@ character_set_client * /; 
 / *！40101 SET character_set_client = utf8 * /; 
 CREATE TABLE`agenzie`（
`agenziaId` varchar（255）NOT NULL，
`userId` varchar（255）NOT NULL，
 PRIMARY KEY（`agenziaId`）
）ENGINE = InnoDB DEFAULT CHARSET = utf8; 
  
 
 
解决方案
您应该可以运行：
 
 $ b $ pre $  show engine innodb status 
  



找到最近的外键错误（可能是大写）。



当然，如果错误信息文本完全无用 Error代码：1215.无法添加外键约束，你不得不想知道为什么开发人员不会立即给你提供这些信息，而不是强迫你去查找它。

b
$ b

---

现在你已经做了，看到了：

2015-02-19 00:51:55 1528表tesoreria /＃sql-12a4_bd的外键约束出错：FOREIGN KEY（userId）REFERENCES utenti（userId）：在引用中找不到索引表所引用的列显示为第一个表表中的列，或列类型与引用的表不匹配。请注意ENUM和SET的内部存储类型在使用> = InnoDB-4.1.12创建的表中更改，旧表中的这些列不能被新表中的这些列引用。

这似乎是两件事情之一。首先是没有索引合并 utenti（userId），如果它是主键，显然不是这种情况。

<第二个是列不匹配，并根据您添加的DDL，似乎可能是罪魁祸首，因为两列被定义为：

`userId` varchar（255）CHARACTER SET latin1 NOT NULL，
`userId` varchar（255）NOT NULL，
  pre> 
 
 
换句话说，它可能是字符集规范的存在使得列成为不同的类型。我建议创建它们完全相同的类型，看看是否解决了这个问题。
 
I have two tables:
Table: utenti
Columns:
userId varchar(255) PK 
password varchar(255)

Table: agenzie
Columns:
agenziaId varchar(255) PK 
userId varchar(255)
When I try to create the foreign key on agenzie, this error message appear:
ALTER TABLE agenzie ADD FOREIGN KEY (userId) REFERENCES utenti(userId)  Error Code: 1215. Cannot add foreign key constraint 0.015 sec
How can I fix that?

Thank you.

EDIT:
DROP TABLE IF EXISTS `utenti`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `utenti` (
`userId` varchar(255) CHARACTER SET latin1 NOT NULL,
`password` varchar(255) CHARACTER SET latin1 NOT NULL,
PRIMARY KEY (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `agenzie`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `agenzie` (
`agenziaId` varchar(255) NOT NULL,
`userId` varchar(255) NOT NULL,
PRIMARY KEY (`agenziaId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

解决方案
You should be able to run:
show engine innodb status
and search for the phrase latest foreign key error (may be upper case).

That should provide more detail on why the constraint creation failed.

Of course, given the utter uselessness of the error message text Error Code: 1215. Cannot add foreign key constraint, you've got to wonder why the developers don't give you that information immediately, rather than forcing you to go looking for it.



Now that you've done that, and seen:

  
2015-02-19 00:51:55 1528 Error in foreign key constraint of table tesoreria/#sql-12a4_bd: FOREIGN KEY (userId) REFERENCES utenti(userId): 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.
It appears to be one of two things. The first is that there's no index incorporating utenti(userId) which is clearly not the case if it's a primary key.

The second is that the columns don't match and, based on your added DDL, it appears that may be the culprit, since the two columns are defined as:
`userId` varchar(255) CHARACTER SET latin1 NOT NULL,
`userId` varchar(255) NOT NULL,
In other words, it may be the presence of the character set specification making the columns a different type. I'd suggest creating them exactly the same type and seeing if that fixes the issue.

                        
这篇关于MySQL：错误代码：1215.无法添加外键约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！