当前位置：theitroad>MySQL：错误 1215 (HY000)：无法添加外键约束

MySQL：错误 1215 (HY000)：无法添加外键约束

声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow 原文地址: http://stackoverflow.com/questions/18930084/
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

提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-08-31 18:56:06 　来源:igfitidea点击:

MySQL : ERROR 1215 (HY000): Cannot add foreign key constraint

mysqlsqldatabase

提问by inherithandle

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 departmentlooks like

我已阅读数据库系统概念，第 6 版，Silberschatz。我将在 MySQL 上的 OS X 上实现第 2 章中显示的大学数据库系统。但是我在创建表时遇到了麻烦course。桌子department看起来像

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 coursecauses the following error.

创建表course会导致以下错误。

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 coursemust exist in primary key column in the table department. But I should have met this error when inserting data.

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

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_namein course table as foreign key after inserting some data?

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

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

编辑：输入set foreign_key_checks=0到mysql>不修复错误。

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

采纳答案by Faishal

The syntax of FOREIGN KEYfor CREATE TABLEis structured as follows:

FOREIGN KEYfor的语法CREATE TABLE结构如下：

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 departmenttable dept_nameshould be VARCHAR(20)

此外，在department表中dept_name应该VARCHAR(20)

More information can be found in the MySQL documentation

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

回答by am0wa

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:

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

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

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

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

回答by workflo

Maybe your dept_namecolumns 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;

回答by ta.speot.is

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_nameto 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 TABLEor ALTER TABLEstatement looks like this:

[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

13.1.17.2. 使用外键约束

... [the] CREATE TABLEorALTER 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

回答by Hyman Davidson

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 并且不小心删除了列的主键状态，并得到了这个错误。

回答by Gray

ERROR 1215 (HY000): Cannot add foreign key constraint

ERROR 1215 (HY000): 无法添加外键约束

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

还值得注意的是，当另一个能力中的外键列的类型与正确表中的列不明确匹配时，您会收到此错误。

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_idfield was defined as:

这是因为该student_id字段被定义为：

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

while the idfield in the studenttable was defined as:

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

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

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

将bigint(20)（从Java产生long的休眠）不兼容int(10) unsigned（Java int）。

回答by g10guang

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 charor 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。我使用 a charset=gbk，但我创建了一个新表，其默认charset=utf8. 所以字符集是不一样的。

ERROR 1215 (HY000): Cannot add foreign key constraint

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

解决它是使用相同的字符集。例如utf8。

回答by vedavyasa k

Just add 'unsigned' for the FOREIGN constraint

只需为 FOREIGN 约束添加 'unsigned'

`FK` int(11) unsigned DEFAULT NULL,

回答by anjaneyulubatta505

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;

回答by EdgeCaseBerg

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

我没有看到任何人明确指出这一点，并且我收到了同样的错误消息，我的问题是我试图向 TEMPORARY 表添加外键。如手册中所述，这是不允许的

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.

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

(emphasis mine)

（强调我的）

相关推荐

mysql GROUP_CONCAT DISTINCT 多列

mysql GROUP_CONCAT DISTINCT 多列

如何从 MySQL 查询返回字段类型？

如何从 MySQL 查询返回字段类型？

MySQL SQL SELECT LIKE（不区分大小写）

MySQL SQL SELECT LIKE（不区分大小写）

MySQL：如何查看表上的所有约束？

MySQL：如何查看表上的所有约束？