MySQL：错误1215（HY000）：无法添加外键约束

Question

我已经阅读了 Silberschatz的 数据库系统概念 ，第6版。我将在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    |       |
+-----------+---------------+------+-----+---------+-------+

创建表将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

在google搜索外键约束之后，我刚刚了解到“外键约束”一词表示来自表中外键列的数据course必须存在于表的主键列中department。但是我应该在插入数据时遇到此错误。

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

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

编辑 ：输入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

Answer 1

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

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

因此，您的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)
    );

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

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