MySQL:错误1215 (HY000):不能添加外键约束。

I have read Database system concepts, 6th edition, Silberschatz. I'm going to implement the university database system shown in chapter 2 on OS X on MySQL. But I have a trouble with creating the table course. the table department looks like

我读过数据库系统概念，第6版，Silberschatz。我将实现大学数据库系统，如第二章所示关于MySQL的OS X。但是我在创建桌面课程时遇到了麻烦。表格部门看起来是这样的。

mysql> select * from department
    -> ;
+------------+----------+-----------+
| dept_name  | building | budget    |
+------------+----------+-----------+
| Biology    | Watson   |  90000.00 |
| Comp. Sci. | Taylor   | 100000.00 |
| Elec. Eng. | Taylor   |  85000.00 |
| Finance    | Painter  | 120000.00 |
| History    | Painter  |  50000.00 |
| Music      | Packard  |  80000.00 |
| Physics    | Watson   |  70000.00 |
+------------+----------+-----------+

mysql> show columns from department
    -> ;
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| dept_name | varchar(20)   | NO   | PRI |         |       |
| building  | varchar(15)   | YES  |     | NULL    |       |
| budget    | decimal(12,2) | YES  |     | NULL    |       |
+-----------+---------------+------+-----+---------+-------+

Creating the table course causes the following error.

创建表过程会导致以下错误。

mysql> create table course
    -> (course_id varchar(7),
    -> title varchar (50),
    -> dept_name varchar(20),
    -> credits numeric(2,0),
    -> primary key(course_id),
    -> foreign key (dept_name) references department);
ERROR 1215 (HY000): Cannot add foreign key constraint

after searching google for foreign key constraint, I have just learned that the word 'foreign key constraint' indicates that data from foreign key column in the table course must exist in primary key column in the table department. But I should have met this error when inserting data.

在搜索了谷歌的外键约束之后，我刚刚了解到，单词“外键约束”表示表过程中来自外键列的数据必须存在于表部门的主键列中。但是我应该在插入数据时遇到这个错误。

If not, why does author make me execute that SQL statement?

如果没有，为什么作者要我执行那个SQL语句?

If I really execute erroneous SQL statement, Does I have to designate dept_name in course table as foreign key after inserting some data?

如果我真的执行了错误的SQL语句，在插入一些数据后，是否需要在课程表中指定dept_name为外键?

EDIT : typing set foreign_key_checks=0 into mysql> does not fix the error.

编辑:在mysql>中输入set foreign_key_check =0不会修复错误。

------------------------
LATEST FOREIGN KEY ERROR
------------------------
2013-09-21 16:02:20 132cbe000 Error in foreign key constraint of table university/course:
foreign key (dept_name) references department):
Syntax error close to:
)
mysql> set foreign_key_checks=0
    -> ;
Query OK, 0 rows affected (0.00 sec)
mysql> create table course
    -> (course_id varchar(7),
    -> title varchar(50),
    -> dept_name varchar(20),
    -> credits numeric(2,0),
    -> primary key(course_id),
    -> foreign key (dept_name) references department);
ERROR 1215 (HY000): Cannot add foreign key constraint

13 个解决方案

#1

The syntax of FOREIGN KEY for CREATE TABLE is structured as follows:

创建表的外键语法结构如下:

FOREIGN KEY (index_col_name)
        REFERENCES table_name (index_col_name,...)

So your MySQL DDL should be:

因此，您的MySQL DDL应该是:

 create table course (
        course_id varchar(7),
        title varchar(50),
        dept_name varchar(20),
        credits numeric(2 , 0 ),
        primary key (course_id),
        FOREIGN KEY (dept_name)
            REFERENCES department (dept_name)
    );

Also, in the department table dept_name should be VARCHAR(20)

而且，在部门表中dept_name应该是VARCHAR(20)

More information can be found in the MySQL documentation

更多信息可以在MySQL文档中找到

#2

When you get this vague error message, you can find out the more specific error by running

当您得到这个模糊的错误消息时，您可以通过运行找到更具体的错误

SHOW ENGINE INNODB STATUS;

The most common reasons are that when creating a foreign key, both the referenced field and the foreign key field need to match:

最常见的原因是在创建外键时，引用字段和外键字段都需要匹配:

• Engine should be the same e.g. InnoDB
• 发动机应该与InnoDB相同
• Datatype should be the same, and with same length.
  e.g. VARCHAR(20) or INT(10) UNSIGNED
• 数据类型应该是相同的，并且长度相同。VARCHAR(20)或INT(10)无符号
• Collation should be the same. e.g. utf8
• 整理应该是一样的。例如utf8
• Unique - Foreign key should refer to field that is unique (usually private) in the reference table.
• 唯一的-外键应该指向引用表中唯一(通常是私有的)字段。

Another cause of this error is:
You have defined a SET NULL condition though some of the columns are defined as NOT NULL.

这个错误的另一个原因是:您已经定义了一个SET NULL条件，尽管有些列被定义为NOT NULL。

#3

Maybe your dept_name columns have different charsets.

也许您的dept_name列有不同的字符集。

You could try to alter one or both of them:

你可以尝试改变其中一个或两个:

ALTER TABLE department MODIFY dept_name VARCHAR(20) CHARACTER SET utf8;
ALTER TABLE course MODIFY dept_name VARCHAR(20) CHARACTER SET utf8;

#4

foreign key (dept_name) references department

This syntax is not valid for MySQL. It should instead be:

此语法对MySQL无效。它应该是:

foreign key (dept_name) references department(dept_name)

MySQL requires dept_name to be used twice. Once to define the foreign column, and once to define the primary column.

MySQL要求dept_name被使用两次。一次定义外列，一次定义主列。

13.1.17.2. Using FOREIGN KEY Constraints

... [the] essential syntax for a foreign key constraint definition in a CREATE TABLE or ALTER TABLE statement looks like this:

…在CREATE TABLE或ALTER TABLE语句中，外键约束定义的基本语法如下所示:

[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name, ...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION

#5

It is also possible to get this error if the foreign key is not a primary key within its own table.

如果外键不是表中的主键，也可以获得此错误。

I did an ALTER TABLE and accidentally removed the primary key status of a column, and got this error.

我做了一个ALTER TABLE，不小心删除了列的主键状态，得到了这个错误。

#6

ERROR 1215 (HY000): Cannot add foreign key constraint

错误1215 (HY000):不能添加外键约束

It is also worth noting that you get this error when the type of the column that is a foreign key in another able doesn't explicitly match the column in the correct table.

同样值得注意的是，当另一个able中的外键列的类型没有显式地匹配正确表中的列时，您会得到这个错误。

For example:

例如:

alter table schoolPersons
         add index FKEF5AB5E532C8FBFA (student_id),
         add constraint FKEF5AB5E532C8FBFA
         foreign key (student_id)
         references student (id);
ERROR 1215 (HY000): Cannot add foreign key constraint

This was because the student_id field was defined as:

这是因为student_id字段定义为:

mysql> desc schoolPersons;
+--------------------+------------+------+-----+---------+----------------+
| Field              | Type       | Null | Key | Default | Extra          |
+--------------------+------------+------+-----+---------+----------------+
| student_id         | bigint(20) | YES  |     | NULL    |                |

while the id field in the student table was defined as:

而student表中的id字段定义为:

mysql> desc persons;
+--------------+----------------------+------+-----+-------------------+-----------------+
| Field        | Type                 | Null | Key | Default           | Extra           |
+--------------+----------------------+------+-----+-------------------+-----------------+
| id           | int(10) unsigned     | NO   | PRI | NULL              | auto_increment  |

The bigint(20) (generated from Java long by hibernate) is not compatible with int(10) unsigned (Java int).

bigint(20)(由hibernate生成的Java long)与int(10)无符号(Java int)不兼容。

#7

It's worth noting that this error can also happen if the target table or column you're using in the REFERENCES portion simply doesn't exist.

值得注意的是，如果您在引用部分中使用的目标表或列根本不存在，也可能发生此错误。

#8

Just add 'unsigned' for the FOREIGN constraint

只需要在外部约束中添加“unsigned”。

`FK` int(11) unsigned DEFAULT NULL,

#9

Below code worked for me

下面的代码对我有用

set @@foreign_key_checks=0;
ALTER TABLE  `table1` ADD CONSTRAINT `table1_fk1` FOREIGN KEY (`coloumn`) REFERENCES `table2` (`id`) ON DELETE CASCADE;

#10

I don't meet the problem as you. But I get the same ERROR Message. So I mark it down here for others' convience.

我不像你一样遇到这个问题。但是我得到了相同的错误信息。我把它标记在下面，以方便其他人。

Check the charset of two table if the column type is char or varchar. I use a charset=gbk, but I create a new table whose default charset=utf8. So the charset is not the same.

如果列类型为char或varchar，请检查两个表的字符集。我使用charset=gbk，但是我创建了一个新表，它的默认字符集=utf8。所以字符集不一样。

ERROR 1215 (HY000): Cannot add foreign key constraint

To solve it is to use the same charset. For example utf8.

要解决这个问题，就要使用相同的字符集。例如utf8。

#11

I don't see anyone stating this explicitly and I had this same error message and my problem was that I was trying to add a foreign key to a TEMPORARY table. Which is disallowed as noted in the manual

我没有看到任何人明确地说明这一点，我有同样的错误消息，我的问题是我试图向临时表添加外键。如手册中所述，哪些是不允许的

Foreign key relationships involve a parent table that holds the central data values, and a child table with identical values pointing back to its parent. The FOREIGN KEY clause is specified in the child table. The parent and child tables must use the same storage engine. They must not be TEMPORARY tables.

外键关系涉及一个包含中心数据值的父表，以及一个指向其父的值相同的子表。外键子句在子表中指定。父表和子表必须使用相同的存储引擎。它们不能是临时的桌子。

(emphasis mine)

(强调我的)

#12

I came across the same issue as well. Not sure why this is working but it indeed works: Try add ENGINE INNODB after your create query.

我也遇到了同样的问题。不知道为什么会这样，但它确实有效:尝试在创建查询之后添加引擎INNODB。

mysql> create table course
-> (course_id varchar(7),
-> title varchar (50),
-> dept_name varchar(20),
-> credits numeric(2,0),
-> primary key(course_id),
-> foreign key (dept_name) references department) ENGINE INNODB;

#13

CONSTRAINT vendor_tbfk_1 FOREIGN KEY (V_CODE) REFERENCES vendor (V_CODE) ON UPDATE CASCADE

this is how it could be... look at the referencing column part. (V_code)

事情就是这样……查看引用列部分。(V_code)