mysql无法添加外键约束

[拼一枪]

create database upcdb;
use database upcdb;
set foreign_key_checks=0;
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
	);

create table instructor
	(ID			varchar(5), 
	 name			varchar(20) not null, 
	 dept_name		varchar(20), 
	 salary			numeric(8,2) check (salary > 29000),
	 primary key (ID),
	 foreign key (dept_name) references department
		on delete set null
	);

create table section
	(course_id		varchar(8), 
         sec_id			varchar(8),
	 semester		varchar(6)
		check (semester in ('Fall', 'Winter', 'Spring', 'Summer')), 
	 year			numeric(4,0) check (year > 1701 and year < 2100), 
	 building		varchar(15),
	 room_number		varchar(7),
	 time_slot_id		varchar(4),
	 primary key (course_id, sec_id, semester, year),
	 foreign key (course_id) references course
		on delete cascade,
	 foreign key (building, room_number) references classroom
		on delete set null
	);

create table teaches
	(ID			varchar(5), 
	 course_id		varchar(8),
	 sec_id			varchar(8), 
	 semester		varchar(6),
	 year			numeric(4,0),
	 primary key (ID, course_id, sec_id, semester, year),
	 foreign key (course_id,sec_id, semester, year) references section
		on delete cascade,
	 foreign key (ID) references instructor
		on delete cascade
	);

create table student
	(ID			varchar(5), 
	 name			varchar(20) not null, 
	 dept_name		varchar(20), 
	 tot_cred		numeric(3,0) check (tot_cred >= 0),
	 primary key (ID),
	 foreign key (dept_name) references department
		on delete set null
	);

create table takes
	(ID			varchar(5), 
	 course_id		varchar(8),
	 sec_id			varchar(8), 
	 semester		varchar(6),
	 year			numeric(4,0),
	 grade		        varchar(2),
	 primary key (ID, course_id, sec_id, semester, year),
	 foreign key (course_id,sec_id, semester, year) references section
		on delete cascade,
	 foreign key (ID) references student
		on delete cascade
	);

create table advisor
	(s_ID			varchar(5),
	 i_ID			varchar(5),
	 primary key (s_ID),
	 foreign key (i_ID) references instructor (ID)
		on delete set null,
	 foreign key (s_ID) references student (ID)
		on delete cascade
	);

create table time_slot
	(time_slot_id		varchar(4),
	 day			varchar(1),
	 start_hr		numeric(2) check (start_hr >= 0 and start_hr < 24),
	 start_min		numeric(2) check (start_min >= 0 and start_min < 60),
	 end_hr			numeric(2) check (end_hr >= 0 and end_hr < 24),
	 end_min		numeric(2) check (end_min >= 0 and end_min < 60),
	 primary key (time_slot_id, day, start_hr, start_min)
	);

create table prereq
	(course_id		varchar(8), 
	 prereq_id		varchar(8),
	 primary key (course_id, prereq_id),
	 foreign key (course_id) references course
		on delete cascade,
	 foreign key (prereq_id) references course
	);

在创建第三个表时，发生了错误
ERROR 1215: Cannot add foreign key constraint
建表的代码是在《数据库系统概念（第六版）》的网站上下载的，求大神指导。

[中国风]

use upcdb;--去掉database

所有外健都少了指定列名，加上就行了 foreign key (dept_name) references department(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(dept_name)
		on delete set null
	);

[bossenc]

按照您的解决办法，其他表和约束都可以成功创建，但是最后一个表prered的外键约束还是无法创建，仍然提示“ ERROR 1215: Cannot add foreign key constraint。 create table prereq (course_id varchar(8), prereq_id varchar(8), primary key (course_id, prereq_id), foreign key (course_id) references course(course_id) on delete cascade, foreign key (prereq_id) references course(prereq_id) ); 暂时未找到原因

[卖水果的net]

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 ); foreign key (prereq_id) references course(prereq_id) 根本就没有这一列。

[bossenc]

谢谢提醒，是我太粗心了，course表里确实没有这个prereq_id这一列，但是还有个问题想请教下，这个创建表和外键索引的语句在sql server下是可以成功执行的，可能是什么原因呢?

[卖水果的net]

引用 4 楼 swinfans 的回复:

谢谢提醒，是我太粗心了，course表里确实没有这个prereq_id这一列，但是还有个问题想请教下，这个创建表和外键索引的语句在sql server下是可以成功执行的，可能是什么原因呢?

不会的，你再核对一下吧，没有这列，理论上也不会成功的；

[bossenc]

创建表的这些语句我最开始就是在SQL Server 2016上执行成功的，如下图所示，course表里确实没有prereq_id这一个字段，但是表prere中却成功建立了外键索引：




然后我在MySQL5.7上执行，就遇到了如上的错误提示。
我已经在sql server上验证过很多次了，确实可以成功执行。

[阳泉酒家小当家]

引用 6 楼 swinfans 的回复:

创建表的这些语句我最开始就是在SQL Server 2016上执行成功的，如下图所示，course表里确实没有prereq_id这一个字段，但是表prere中却成功建立了外键索引： 然后我在MySQL5.7上执行，就遇到了如上的错误提示。 我已经在sql server上验证过很多次了，确实可以成功执行。

应该是在语法上有细微的差别，sql server，oracle，mysql在具体的语法上，会有很大的差别，虽然概念是一样的。

[weixin_39064702]

mysql没有检查约束你怎么加的呢 你这个是sqlsever建表的吧