MySQL BUG集锦

错误提示:1215 - Cannot add foreign key constraint

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
#建学生信息表 student 
create table student(
sno varchar(20) not null primary key,
sname varchar(20) not null,
ssex varchar(20) not null,
sbirthday datetime,
class varchar(20)
);
#建立教师表
create table teacher
(
tno varchar(20) not null primary key,
tname varchar(20) not null,
tsex varchar(20) not null,
tbirthday datetime,
prof varchar(20),
depart varchar(20) not null
);
#建立课程表 course
create table course
(
cno varchar(20) not null primary key,
cname varchar(20) not null,
tno varchar(20) not null,
foreign key(tno) references teacher(tno)
);
#建立成绩表
create table score
(
sno varchar(20),
foreign key(sno) references student(sno),
cno varchar(20) not null,
foreign key(cno) references course(cno),
degree decimal
);

创建成绩表时出错,错误提示:1215 - Cannot add foreign key constraint

问题分析:

产生错误提示:1215 - Cannot add foreign key constraint的主要原因:

(1)外键对应的字段数据类型不一致(类型一致)

(2)设置外键时“删除时”设置为“SET NULL”

(3)两张表的存储引擎不一致(同为innoDB)

1
2
3
4
5
6
7
8
create table score 
(
sno varchar(20),
foreign key(sno) references student(sno),
-- cno varchar(20) not null,
-- foreign key(cno) references course(cno),
-- degree decimal
);

经过测试,在设置2、3行时出现问题。但是四个表的插入语句一起执行时不会报错。

解决办法:

暂未弄清BUG出现的原因,解决的办法就是学生表和成绩表同时打。

  • Copyright: Copyright is owned by the author. For commercial reprints, please contact the author for authorization. For non-commercial reprints, please indicate the source.

扫一扫,分享到微信

微信分享二维码
  • Copyrights © 2021 John Doe
  • Visitors: | Views:

请我喝瓶快乐水吧~