[分享]MySQL中使用外键_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方技术论坛-国内领先的Weblogic、Tuxedo中间件技术论坛
联动北方-国内领先的云技术服务提供商
»  游客             当前位置：  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 2190 | 回复: 0   主题： [分享]MySQL中使用外键        下一篇 
shunli
注册用户
等级：新兵
经验：36
发帖：59
精华：0
注册：2011-9-23
状态：离线
发送短消息息给shunli 加好友    发送短消息息给shunli 发消息
发表于： IP：您无权察看 2015-1-6 9:42:30 | [全部帖] [楼主帖] 楼主

关于外键：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);
Query OK, 1 row affected
Query OK, 1 row affected
Query OK, 1 row affected
Query OK, 1 row affected
Query OK, 1 row affected
Query OK, 1 row affected
Query OK, 1 row affected
Query OK, 1 row affected
Query OK, 1 row affected
Query OK, 1 row affected
Query OK, 1 row affected
Query OK, 1 row affected
Query OK, 1 row affected
Query OK, 1 row affected
Query OK, 1 row affected
Query OK, 1 row affected
Query OK, 1 row affected
Query OK, 1 row affected


插入成功！！！

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


对于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>


--转自 北京联动北方科技有限公司


赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码： 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论

Weblogic中间件技术论坛

 |

Tuxedo中间件技术论坛

 |

数据库论坛

 |

Java论坛

 |

Linux/unix论坛

 |

网站地图


Powered by LandingBJ     @ 2008-2018     联动北方   
京ICP证号090597    京ICP备案09017624号    京公网安备110112000093号    
400-810-2327     Email：service@landingbj.com   

Powered by landingbj @ 2008-2015 联动北方
京ICP证号090597 京ICP备案09017624号 京公网安备110112000093号
86-27-87056338 Email：service@landingbj.com