MySQL 错误代码：1215。无法添加外键约束（外键）

Error Code: 1215. Cannot add foreign key constraint (foreign keys)

提问by GEORGE Reed

CREATE DATABASE my_db;

CREATE TABLE class (
  classID int NOT NULL AUTO_INCREMENT,
  nameClass varchar(255),
  classLeader varchar(255),
  FOREIGN KEY (classLeader) REFERENCES student(studentID),
  PRIMARY KEY (classID));

CREATE TABLE student (
  studentID int NOT NULL AUTO_INCREMENT,
  lastName varchar(255),
  firstName varchar(255),
  classID int,
  FOREIGN KEY (classID) REFERENCES class(classID),
  PRIMARY KEY (studentID));

I am trying to ensure data consistency between the tables by using foreign key so that the DBMS can check for errors; however, it seems we can't do that for some reason. What's the error and is there an alternative? Also, when I fill a table that has a foreign key, I can't fill the field that's reserved for the foreign key(s), right? Also, is a foreign key considered to be a key at all?

我试图通过使用外键来确保表之间的数据一致性，以便 DBMS 可以检查错误；但是，由于某种原因，我们似乎不能这样做。错误是什么，是否有替代方法？另外，当我填充具有外键的表时，我无法填充为外键保留的字段，对吗？另外，外键是否被认为是键？

回答by spencer7593

The most likely issue is this line:

最可能的问题是这一行：

FOREIGN KEY (classLeader) REFERENCES student(studentID),

The datatype of classLeader is VARCHAR(255). That has to match the datatypeof the referenced column... student.studentID. And of course, the studenttable has to exist, and the studentIDcolumn has to exist, and the studentIDcolumn should be the PRIMARY KEY of the student table (although I believe MySQL allows this to be a UNIQUE KEY, rather than a PRIMARY KEY, or even just have an index on it.)

classLeader 的数据类型是 VARCHAR(255)。这有匹配数据类型的引用的列... student.studentID。当然，student表必须存在，studentID列必须存在，并且studentID列应该是学生表的 PRIMARY KEY（虽然我相信 MySQL 允许这是一个 UNIQUE KEY，而不是 PRIMARY KEY，甚至只要有一个索引就可以了。）

In any case, what's missing here is the output from SHOW CREATE TABLE student;

无论如何，这里缺少的是来自 SHOW CREATE TABLE student;

---

---

There's a datatype mismatch.

存在数据类型不匹配。

The classLeader VARCHAR(255)column cannot be a foreign key reference to studentID INT.

该classLeader VARCHAR(255)列不能是对 的外键引用studentID INT。

The datatypes of the two columns has to match.

两列的数据类型必须匹配。

回答by Rohan

You are getting this error because of in FOREIGN KEY (classLeader) REFERENCES student(studentID)datatype of studentIDand classLeaderis different.Datatype of primary key column and foreign key column must be same.

由于 in和in 的FOREIGN KEY (classLeader) REFERENCES student(studentID)数据类型不同，您收到此错误。主键列和外键列的数据类型必须相同。studentIDclassLeader

From MySQL Site:

从 MySQL 站点：

Corresponding columns in the foreign key and the referenced key must have similar data types. The size and sign of integer types must be the same. The length of string types need not be the same. For nonbinary (character) string columns, the character set and collation must > be the same.

外键和引用键中对应的列必须具有相似的数据类型。整数类型的大小和符号必须相同。字符串类型的长度不必相同。对于非二进制（字符）字符串列，字符集和排序规则必须 > 相同。

回答by John Prawyn

Though it is a late answer, I hope it would help few people.

虽然这是一个迟到的答案，但我希望它能帮助少数人。

I faced the same issue. But, here the Data types were also same.

我遇到了同样的问题。但是，这里的数据类型也是相同的。

But, When I checked the create table statement, I found that One table created using a engine called "MyISAM" and another one was using "InnoDB".

但是，当我检查 create table 语句时，我发现一个表使用名为“MyISAM”的引擎创建，另一个使用“InnoDB”。

SHOW CREATE TABLE <TABLE NAME>

Then I changed both tables engine to "InnoDB" and it worked(I was able create Foreign Key)

然后我将两个表引擎都更改为“InnoDB”并且它起作用了（我能够创建外键）

回答by MS Ibrahim

The error gets resolved:

错误得到解决：

To create a Foreign key for a table like this

为这样的表创建外键

CREATE TABLE USERS_SO (
    USERNAME VARCHAR(10) NOT NULL,
    PASSWORD VARCHAR(32) NOT NULL,
    ENABLED SMALLINT,
    PRIMARY KEY (USERNAME)
);

The below code works fine

下面的代码工作正常

CREATE TABLE AUTHORITIES_SO (
    USERNAME VARCHAR(10) NOT NULL,
    AUTHORITY VARCHAR(10) NOT NULL,
    FOREIGN KEY (USERNAME) REFERENCES USERS_SO(USERNAME)
);

回答by stevensagaar

Make sure you have a header and footer in sql dump otherwise you will see all sort of errors when restoring database

确保在 sql dump 中有页眉和页脚，否则在恢复数据库时你会看到各种错误

Header should look something like below -:

标题应如下所示 -：

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

and footer should look something like below -:

和页脚应该如下所示 -：

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Hope the above helps

希望以上有帮助

Cheers S

干杯

回答by Duracell De Monaco

Make sure if yourtable is using InnoDB engine

确定您的表是否使用 InnoDB 引擎

First, verify if engine is INNODB or not:

首先，验证引擎是否为 INNODB：

SHOW CREATE TABLE yourtable

If not change the engine to InnoDB:

如果没有将引擎更改为 InnoDB：

ALTER TABLE yourtable ENGINE=INNODB;

回答by Adi

Along with all the properties mentioned in the other answers, the CHARSET of both tables too should match for successful mapping the foreign keys between them.

连同其他答案中提到的所有属性，两个表的 CHARSET 也应该匹配以成功映射它们之间的外键。

回答by TheFatMan

Set end reference point of the key to 'Unique'

将键的结束参考点设置为“唯一”

回答by Niyas

Remove the Engine, CHARSET, COLLATEfrom Query , then checkout . It works for me.

删除Engine, CHARSET, COLLATEfrom Query ，然后 checkout 。这个对我有用。