文档章节

MySQL使用外键

秋风醉了
 秋风醉了
发布于 2014/09/09 15:58
字数 1905
阅读 240
收藏 1

关于外键:http://my.oschina.net/xinxingegeya/blog/276181

前言:其实通篇就主要写了两个异常的处理

  • Cannot add or update a child row

  • Cannot delete or update a parent row

 

mysql-5.6新建以下数据库

mysql> CREATE TABLE student(
sid varchar(10) not null,
sname varchar(10) not null,
sage datetime not null,
ssex enum('男','女') not null,
PRIMARY KEY (sid)
);

CREATE TABLE teacher(
tid varchar(10) not null,
tname varchar(10) not null,
PRIMARY KEY (tid)
);

CREATE TABLE course(
cid varchar(10) not null,
cname varchar(10),
tid varchar(10) COMMENT 'teacher的id,是表的外键,为空说明该课程没有授课老师',
PRIMARY KEY (cid),
CONSTRAINT course_foreign_key FOREIGN KEY (tid) REFERENCES teacher (tid)
);

CREATE TABLE student_course(
sid varchar(10) not null COMMENT '学生的id',
cid varchar(10) not null COMMENT '课程的id',
score double default 0 COMMENT '学生该课程的分数',
PRIMARY KEY (sid,cid),
INDEX sid_index (sid),
INDEX cid_index (cid),
CONSTRAINT sid_f_key FOREIGN KEY (sid) REFERENCES student (sid),
CONSTRAINT cid_f_key FOREIGN KEY (cid) REFERENCES course (cid)
);
Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 0 rows affected

mysql> show tables;
+-----------------+
| Tables_in_test9 |
+-----------------+
| course          |
| student         |
| student_course  |
| teacher         |
+-----------------+
4 rows in set

 

数据库建好了,现在插入数据:

mysql> insert into student values('01' , '赵一' , '1990-01-01' , '男');
insert into student values('02' , '赵二' , '1990-01-01' , '男');
insert into student values('03' , '赵三' , '1990-01-01' , '女');
insert into student values('04' , '赵四' , '1990-01-01' , '女');
insert into teacher values('100' , '张三');
insert into teacher values('101' , '李四');
insert into teacher values('102' , '王五');
insert into course values('1234' , '数学' , '100');
insert into course values('1235' , '语文' , '101');
insert into course values('1236' , '英语' , '102');
insert into course values('1237' , '政治' , '101');
insert into course (cid,cname) values('1238' , '生物');
insert into course (cid,cname) values('1230' , '历史');
insert into course (cid,cname) values('1231' , '物理');
insert into student_course values('01' , '1234' , 80);
insert into student_course values('01' , '1235' , 90);
insert into student_course values('02' , '1234' , 80);
insert into student_course values('02' , '1235' , 90);

插入成功!!!

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

对于course表,如果其表的定义语句为如下所示:

CREATE TABLE `course` (
  `cid` varchar(10) NOT NULL,
  `cname` varchar(10) DEFAULT NULL,
  `tid` varchar(10) DEFAULT '' COMMENT 'teacher的id,是表的外键,为空(空字符,不是null)说明该课程没有授课老师',
  PRIMARY KEY (`cid`),
  KEY `course_foreign_key` (`tid`),
  CONSTRAINT `course_foreign_key` FOREIGN KEY (`tid`) REFERENCES `teacher` (`tid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

不同在于该列的定义上,tid列的默认为空字符串。。。

`tid` varchar(10) DEFAULT '' COMMENT 'teacher的id,是表的外键,为空(空字符,不是null)说明该课程没有授课老师',

那么执行如下插入语句:

mysql> insert into course (cid,cname,tid) values('1231' , '物理','');
1452 - Cannot add or update a child row: a foreign key constraint fails (`test7`.`course`, CONSTRAINT `course_foreign_key` FOREIGN KEY (`tid`) REFERENCES `teacher` (`tid`))
mysql> insert into course (cid,cname) values('1231' , '物理');
1452 - Cannot add or update a child row: a foreign key constraint fails (`test7`.`course`, CONSTRAINT `course_foreign_key` FOREIGN KEY (`tid`) REFERENCES `teacher` (`tid`))

这样会报错,Cannot add or update a child row。

在外键约束建立成功的前提下,要确保course表中tid字段插入的值一定要在teacher表中存在,如果不存在,tid字段的值应为null,而不是默认的空字符串。如果强行执行插入,则违反了数据完整性的原则,一定会报错。

 

上面是执行插入语句,现在来看执行更新语句

如下所示:

mysql> select * from course;
+------+-------+------+
| cid  | cname | tid  |
+------+-------+------+
| 1230 | 历史  | NULL |
| 1231 | 物理  | NULL |
| 1234 | 数学  | NULL |
| 1235 | 语文  | 109  |
| 1236 | 英语  | 102  |
| 1237 | 政治  | 109  |
| 1238 | 生物  | NULL |
+------+-------+------+
7 rows in set

mysql> select * from teacher;
+-----+----------+
| tid | tname    |
+-----+----------+
| 102 | 王五     |
| 109 | 李麻子啊 |
+-----+----------+
2 rows in set

mysql> update course set tid = '103' where cid = '1236';
1452 - Cannot add or update a child row: a foreign key constraint fails (`test9`.`course`, CONSTRAINT `course_foreign_key` FOREIGN KEY (`tid`) REFERENCES `teacher` (`tid`) ON DELETE SET NULL ON UPDATE CASCADE)
mysql> update course set tid = '109' where cid = '1236';
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from course;
+------+-------+------+
| cid  | cname | tid  |
+------+-------+------+
| 1230 | 历史  | NULL |
| 1231 | 物理  | NULL |
| 1234 | 数学  | NULL |
| 1235 | 语文  | 109  |
| 1236 | 英语  | 109  |
| 1237 | 政治  | 109  |
| 1238 | 生物  | NULL |
+------+-------+------+
7 rows in set

mysql>

当执行这条语句时update course set tid = '103' where cid = '1236';外键tid = '103',在teacher表中根本不存在,违反了数据完整性原则,所以报错。

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

 

现在我们回到正常的sql语句上来:

看删除语句:

mysql> select * from teacher;
+-----+-------+
| tid | tname |
+-----+-------+
| 100 | 张三  |
| 101 | 李四  |
| 102 | 王五  |
+-----+-------+
3 rows in set

mysql> select * from course;
+------+-------+------+
| cid  | cname | tid  |
+------+-------+------+
| 1230 | 历史  | NULL |
| 1231 | 物理  | NULL |
| 1234 | 数学  | 100  |
| 1235 | 语文  | 101  |
| 1236 | 英语  | 102  |
| 1237 | 政治  | 101  |
| 1238 | 生物  | NULL |
+------+-------+------+
7 rows in set

mysql> delete from teacher;
1451 - Cannot delete or update a parent row: a foreign key constraint fails (`test9`.`course`, CONSTRAINT `course_foreign_key` FOREIGN KEY (`tid`) REFERENCES `teacher` (`tid`))
mysql> delete from teacher where tid = '100';
1451 - Cannot delete or update a parent row: a foreign key constraint fails (`test9`.`course`, CONSTRAINT `course_foreign_key` FOREIGN KEY (`tid`) REFERENCES `teacher` (`tid`))
mysql>

报的错误具体如下:

Cannot delete or update a parent row

现在删除失败,是因为外键约束的事件触发机制为RESTRICT,我们设想应该是这样的,当删除老师时,course表中tid字段置为null,这样还是比较合理的。

mysql> alter table course drop foreign key course_foreign_key;
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table course;
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                                       |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| course | CREATE TABLE `course` (
  `cid` varchar(10) NOT NULL,
  `cname` varchar(10) DEFAULT NULL,
  `tid` varchar(10) DEFAULT NULL COMMENT 'teacher的id,是表的外键,为空说明该课程没有授课老师',
  PRIMARY KEY (`cid`),
  KEY `course_foreign_key` (`tid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

mysql> alter table course add constraint course_foreign_key foreign key (tid) references teacher(tid) on delete SET NULL;
Query OK, 7 rows affected
Records: 7  Duplicates: 0  Warnings: 0

mysql> show create table course;
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                                                                                                                                             |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| course | CREATE TABLE `course` (
  `cid` varchar(10) NOT NULL,
  `cname` varchar(10) DEFAULT NULL,
  `tid` varchar(10) DEFAULT NULL COMMENT 'teacher的id,是表的外键,为空说明该课程没有授课老师',
  PRIMARY KEY (`cid`),
  KEY `course_foreign_key` (`tid`),
  CONSTRAINT `course_foreign_key` FOREIGN KEY (`tid`) REFERENCES `teacher` (`tid`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

mysql>

现在执行删除语句

mysql> delete from teacher where tid='100';
Query OK, 1 row affected

mysql> select * from course;
+------+-------+------+
| cid  | cname | tid  |
+------+-------+------+
| 1230 | 历史  | NULL |
| 1231 | 物理  | NULL |
| 1234 | 数学  | NULL |
| 1235 | 语文  | 101  |
| 1236 | 英语  | 102  |
| 1237 | 政治  | 101  |
| 1238 | 生物  | NULL |
+------+-------+------+
7 rows in set

执行成功!!!

 

现在看更新语句:

mysql> update teacher set tname = '李麻子啊' where tid='101';
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from teacher;
+-----+----------+
| tid | tname    |
+-----+----------+
| 101 | 李麻子啊 |
| 102 | 王五     |
+-----+----------+
2 rows in set

mysql> update teacher set tid = '109' where tid='101';
1451 - Cannot delete or update a parent row: a foreign key constraint fails (`test9`.`course`, CONSTRAINT `course_foreign_key` FOREIGN KEY (`tid`) REFERENCES `teacher` (`tid`) ON DELETE SET NULL)

当更新tid字段时报错,这是因为外键约束对于update的事件响应是RESTRICT,这里应该改为级联。

mysql> alter table course drop foreign key course_foreign_key;
alter table course add constraint course_foreign_key foreign key (tid) references teacher(tid) on update CASCADE;
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 7 rows affected
Records: 7  Duplicates: 0  Warnings: 0

mysql> show create table course;
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                                                                                                                                            |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| course | CREATE TABLE `course` (
  `cid` varchar(10) NOT NULL,
  `cname` varchar(10) DEFAULT NULL,
  `tid` varchar(10) DEFAULT NULL COMMENT 'teacher的id,是表的外键,为空说明该课程没有授课老师',
  PRIMARY KEY (`cid`),
  KEY `course_foreign_key` (`tid`),
  CONSTRAINT `course_foreign_key` FOREIGN KEY (`tid`) REFERENCES `teacher` (`tid`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

mysql>

现在重新执行更新语句

mysql> update teacher set tid = '109' where tid='101';
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from course ;
+------+-------+------+
| cid  | cname | tid  |
+------+-------+------+
| 1230 | 历史  | NULL |
| 1231 | 物理  | NULL |
| 1234 | 数学  | NULL |
| 1235 | 语文  | 109  |
| 1236 | 英语  | 102  |
| 1237 | 政治  | 109  |
| 1238 | 生物  | NULL |
+------+-------+------+
7 rows in set

更新成功!!

 

其实,在course表中外键tid的事件机制应为当删除时为SET NULL,更新时为CASCADE

mysql> alter table course drop foreign key course_foreign_key;
alter table course add constraint course_foreign_key foreign key (tid) references teacher(tid) on delete SET NULL on update CASCADE;
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 7 rows affected
Records: 7  Duplicates: 0  Warnings: 0

mysql> show create table course;
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                                                                                                                                                               |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| course | CREATE TABLE `course` (
  `cid` varchar(10) NOT NULL,
  `cname` varchar(10) DEFAULT NULL,
  `tid` varchar(10) DEFAULT NULL COMMENT 'teacher的id,是表的外键,为空说明该课程没有授课老师',
  PRIMARY KEY (`cid`),
  KEY `course_foreign_key` (`tid`),
  CONSTRAINT `course_foreign_key` FOREIGN KEY (`tid`) REFERENCES `teacher` (`tid`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

mysql>

alter table course add constraint course_foreign_key foreign key (tid) references teacher(tid) on delete SET NULL on update CASCADE;

=====END=====

© 著作权归作者所有

共有 人打赏支持
秋风醉了
粉丝 237
博文 578
码字总数 419908
作品 0
朝阳
程序员
Mysql外键和索引的疑问

最近的一个项目是将原项目的Oracle版改成Mysql版,在使用Mysql时也到了不少问题,目前比较大的疑惑是Mysql的外键和索引。 1.为了使用事务,数据库引擎采用innodb,也使用了外键,但Mysql外键...

TaleTsai
2012/12/18
2.3K
4
MySQL学习笔记一

MySQL目录结构 配置my.ini MySQL5.7的my.ini位于ProgramDataMySQLMySQL Server 5.7目录下(可能有的版本的my.ini就在安装目录下),该该目录下还有一个data目录存放我们的创建的数据库。 打开...

Aaron_DMC
2016/12/16
27
0
Mysql索引与键

0.主键与索引的不同 主键在物理层面上只有两个用途: 惟一地标识一行;作为一个可以被外键有效引用的对象。 索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分),它们包含着...

a_liujin
2016/06/30
0
0
MYSQL外键(Foreign Key)的使用

在MySQL 3.23.44版本后,InnoDB引擎类型的表支持了外键约束。 外键的使用条件: 1.两个表必须是InnoDB表,MyISAM表暂时不支持外键(据说以后的版本有可能支持,但至少目前不支持); 2.外键列...

liangtee
2012/10/27
0
0
6、MySQL字段约束介绍

6、MySQL字段约束 上一章简要介绍了关于MySQL关于多表查询的相关操作介绍,本章内容将在创建数据表的时候关于定义的相关字段进行约束操作。 一、字段字段修饰符的使用 1.1 null和not null修饰...

CARYFLASH
2017/11/20
0
0

没有更多内容

加载失败,请刷新页面

加载更多

Java日期和时间获取问题

获取年月日时分秒 Calendar cal = Calendar.getInstance();//获取年int year = cal.get(Calendar.YEAR);//获取月,范围是0-11,最后使用需+1int month = cal.get(Cal...

lanyu96
28分钟前
9
0
Ceph学习笔记2-在Kolla-Ansible中使用Ceph后端存储

环境说明 使用Kolla-Ansible请参考《使用Kolla-Ansible在CentOS 7单节点上部署OpenStack Pike》; 部署Ceph服务请参考《Ceph学习笔记1-Mimic版本多节点部署》。 配置Ceph 以osdev用户登录: ...

LastRitter
32分钟前
8
0
OSChina 周二乱弹 —— 老司机表示右手无处安放

Osc乱弹歌单(2018)请戳(这里) 【今日歌曲】 @花间小酌 :分享李宗盛的单曲《鬼迷心窍》--春风再美也比不上你的笑 《鬼迷心窍》 - 李宗盛 手机党少年们想听歌,请使劲儿戳(这里) 每天早...

小小编辑
44分钟前
314
5
复习2

10月22日任务 awk 中使用外部shell变量 awk 合并一个文件 把一个文件多行连接成一行 awk中gsub函数的使用 awk 截取指定多个域为一行 过滤两个或多个关键词 用awk生成以下结构文件 awk用print...

hhpuppy
52分钟前
5
0
原型模式

1、原型模式-定义 用原型实例指定创建对象的种类,并且通过拷贝这些原型创建新的对象 克隆(浅度克隆->拷贝值类型或者引用,深度克隆->创建新的对象,开辟新的内存) 例如客户端知道抽象Pro...

阿元
今天
63
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部