MySQL：ERROR 1215（HY000）：无法添加外键约束 [英] MySQL : ERROR 1215 (HY000): Cannot add foreign key constraint

问题描述

我已阅读数据库系统概念，第6版， Silberschatz 。我将实现大学数据库系统，如第2章中关于MySQL上的OS X所示。但我有一个麻烦，创建表课程。表部门看起来像

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

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

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

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

外键搜索谷歌后约束，我刚刚得知这个词外键约束表示当然必须在主键列存在于表的表从外键列数据 department 。但是我应该在插入数据时遇到这个错误。

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.

如果没有，为什么作者让我执行那条SQL语句？

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

如果我真的执行错误的SQL语句，我必须在课程表中指定 dept_name 一些数据？

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

EDIT ：在<$ c $中输入 set foreign_key_checks = 0 c> mysql> 不能修复错误。

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

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

推荐答案

FOREIGN KEY 对于的语法如下：CREATE TABLE 结构如下：

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

所以你的MySQL DDL应该是：

So your MySQL DDL should be:

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

此外，在部门 c $ c> dept_name 应为 VARCHAR（20）

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

位于 MySQL文档中

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