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

2021-11-20 00:00:00 sql database mysql

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

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

在google上搜索外键约束后，我才知道外键约束"这个词表示表中外键列的数据course必须存在于表中的主键列中部门.但是我在插入数据时应该会遇到这个错误.

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 :在 mysql> 中输入 set foreign_key_checks=0 不能修复错误.

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

推荐答案

CREATE TABLE的FOREIGN KEY的语法结构如下:

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

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

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

More information can be found in the MySQL documentation

相关文章