删除/更新数据对数据的影响

删除/更新数据对数据的影响

表数据进行删除过程中由于外键的影响导致主从表中数据删除约束也不一样，三种常见的删除影响有主从表同步删除、从表有数据不允许主表删除、主表删除从表对应的列显示为Null

cascade方式

在主表上update/delete记录时，同步update/delete从表的匹配记录

构造数据

#从表创建
create table employee (
id int(3),
name varchar(10),
deptno int(4),
CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES dept (deptno)
On delete cascade  on update cascade
)ENGINE=INNODB;
#主表创建
create table dept (
deptno int(4),
deptname varchar(10),
CONSTRAINT pk_deptno PRIMARY KEY(deptno)
)ENGINE=INNODB;
########新增数据########
-- ----------------------------
-- Records of dept
-- ----------------------------
INSERT INTO `deptw` VALUES ('1', '测试');
INSERT INTO `deptw` VALUES ('2', '开发');

-- ----------------------------
-- Records of employee
-- ----------------------------
INSERT INTO `employee` VALUES ('1', '张三', '1');
INSERT INTO `employee` VALUES ('2', '李四', '2');
INSERT INTO `employee` VALUES ('3', '王五', '1');

删除/更新数据

delete from deptw where deptno =1
###########结果展示############
主表的部门号为1的数据删除同步删除从表中的所在部门为1的张三和王五
update  deptw set deptno =3
###########结果展示############
主表的部门号为1的数据更新为3同步更新从表中的所在部门为2的李四为部门号3

restrict方式

如果从表中有匹配的记录,则不允许对主表对应候选键进行update/delete操作

构造数据

#从表创建
create table employee (
id int(3),
name varchar(10),
deptno int(4),
CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES dept (deptno)
On delete restrict  on update restrict
)ENGINE=INNODB;
#主表创建
create table dept (
deptno int(4),
deptname varchar(10),
CONSTRAINT pk_deptno PRIMARY KEY(deptno)
)ENGINE=INNODB;
########新增数据########
-- ----------------------------
-- Records of dept
-- ----------------------------
INSERT INTO `dept` VALUES ('1', '测试');
INSERT INTO `dept` VALUES ('2', '开发');

-- ----------------------------
-- Records of employee
-- ----------------------------
INSERT INTO `employee` VALUES ('1', '张三', '1');
INSERT INTO `employee` VALUES ('2', '李四', '2');
INSERT INTO `employee` VALUES ('3', '王五', '1');

删除/更新数据

delete from deptw where deptno =1
###########结果展示############
[Err] 1451 - Cannot delete or update a parent row: a foreign key constraint fails (`company`.`employee`, CONSTRAINT `fk_deptno` FOREIGN KEY (`deptno`) REFERENCES `dept` (`deptno`))
从表中有数据无法删除主表的数据
update  dept set deptno =3 where deptno=1
###########结果展示############
[Err] 1451 - Cannot delete or update a parent row: a foreign key constraint fails (`company`.`employee`, CONSTRAINT `fk_deptno` FOREIGN KEY (`deptno`) REFERENCES `deptw` (`deptno`))
从表中有数据无法更新主表的数据

Set Null方式

在主表上update/delete记录时，将从表上匹配记录的列设为null

构造数据

#从表创建
create table employee (
id int(3),
name varchar(10),
deptno int(4),
CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES dept (deptno)
On delete set null  on update set null
)ENGINE=INNODB;
#主表创建
create table dept (
deptno int(4),
deptname varchar(10),
CONSTRAINT pk_deptno PRIMARY KEY(deptno)
)ENGINE=INNODB;
########新增数据########
-- ----------------------------
-- Records of dept
-- ----------------------------
INSERT INTO `dept` VALUES ('1', '测试');
INSERT INTO `dept` VALUES ('2', '开发');

-- ----------------------------
-- Records of employee
-- ----------------------------
INSERT INTO `employee` VALUES ('1', '张三', '1');
INSERT INTO `employee` VALUES ('2', '李四', '2');
INSERT INTO `employee` VALUES ('3', '王五', '1');

删除/更新数据

delete from deptw where deptno =1
###########结果展示############
-- ----------------------------
-- Records of employee
-- ----------------------------
id  name deptno
1	张三	Null
2	李四	2
3	王五	Null
从表中有数据部门号显示为Null
update  dept set deptno =3
###########结果展示############
id  name deptno
1	张三	Null
2	李四	Null
3	王五	Null
从表中中部门号为2的数据更新为Null