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

本文介绍了MySQL:错误 1215 (HY000):无法添加外键约束的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着跟版网的小编来一起学习吧！

问题描述

我已阅读数据库系统概念，第 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

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

问题描述

推荐答案

相关文档推荐