发生错误1005 (HY000)：无法创建表，在研究<<Database系统Concepts>>6ed时

Question

create table classroom
    (building       varchar(15),
     room_number        varchar(7),
     capacity       numeric(4,0),
     primary key (building, room_number)
    );

create table department
    (dept_name      varchar(20), 
     building       varchar(15), 
     budget             numeric(12,2) check (budget > 0),
     primary key (dept_name)
    );

前两个造句是对的。但在

create table course
    (course_id      varchar(8), 
     title          varchar(50), 
     dept_name      varchar(20),
     credits        numeric(2,0) check (credits > 0),
     primary key (course_id),
     foreign key (dept_name) references department
        on delete set null
    );

它发生错误1005 (HY000)：无法创建表.我不知道怎么修好它。显然，外键的数据类型是一致的。

Answer 1

要获取有关错误的详细信息，请运行SHOW ENGINE INNODB STATUS\G并查找LATEST FOREIGN KEY ERROR。应该是这样的：

------------------------
LATEST FOREIGN KEY ERROR
------------------------
2013-12-04 09:50:19 139428000 Error in foreign key constraint of table test/course:
foreign key (dept_name) references department
        on delete set null
    ):
Syntax error close to:

        on delete set null
    )

解决方案是指定父表的列名，如下所示：

create table course
    (course_id      varchar(8), 
     title          varchar(50), 
     dept_name      varchar(20),
     credits        numeric(2,0) check (credits > 0),
     primary key (course_id),
     foreign key (dept_name) references department (dept_name) 
        on delete set null
    );

您应该知道，您的CHECK语句中的CREATE TABLE约束实际上不会做任何事情。如手册中所述

所有存储引擎都解析了CHECK子句，但忽略了该子句。