错误代码:1215。不能添加外键约束(外键)

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 insure 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能够检查错误;然而，由于某些原因，我们似乎做不到。有什么错误，还有其他选择吗?另外，当我填充一个有外键的表时，我不能填充为外键保留的字段，对吗?另外，外键被认为是关键吗?

7 个解决方案

#1

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 datatype of the referenced column... student.studentID. And of course, the student table has to exist, and the studentID column has to exist, and the studentID column 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.)

类leader的数据类型为VARCHAR(255)。必须匹配引用列的数据类型……student.studentID。当然,学生表必须存在,studentID列必须存在,studentID列应该是学生表的主键(虽然我认为MySQL允许这是一个独特的关键,而不是主键,或者只是有一个索引)。

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.

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

#2

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

你之所以会犯这个错误，是因为在外钥(classLeader)引用中学生(studentID)数据类型和classLeader是不同的。主键列和外键列的数据类型必须相同。

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.

#3

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)
);

#4

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

确保在sql转储中有页眉和页脚，否则在还原数据库时将看到所有类型的错误

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

欢呼声年代

#5

Set end reference point of the key to 'Unique'

将键的端点引用点设置为“Unique”

#6

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”，它成功了(我可以创建外键)

#7

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

从查询中删除引擎、字符集、排序，然后签出。它适合我。

#1