一：搭建环境
1）创建籍贯表
create table nativeplace
(
nid number(2) primary key,
np_name varchar2(20) not null
);
insert into nativeplace values(1,'heilongjiang');
insert into nativeplace values(2,'jilin');
insert into nativeplace values(3,'liaoning');
insert into nativeplace values(4,'beijing');
2）创建学生表
CREATE TABLE student
(
sno NUMBER(5) ,
sname VARCHAR2(20),
native_place number(2),
constraint fk_student foreign key (native_place) REFERENCES nativeplace (nid)
);
insert into student values(1,'zhangsan',1);
insert into student values(2,'lisi',2);
insert into student values(3,'wangwu',3);
insert into student values(4,'zhaoliu',3);
commit;
二：应用测试
1）因为nid=4的记录没有外键引用它，所以更新成功
HR@ORA11GR2>update nativeplace set nid=41 where nid=4;
1 row updated.
HR@ORA11GR2>
2）因为nid=3的记录有外键引用它所以失败
HR@ORA11GR2>update nativeplace set nid=31 where nid=3;
update nativeplace set nid=31 where nid=3
*
ERROR at line 1:
ORA-02292: integrity constraint (HR.FK_STUDENT) violated - child record found
HR@ORA11GR2>
HR@ORA11GR2>rollback;
Rollback complete.
HR@ORA11GR2>
4）删除student表的外键
HR@ORA11GR2>alter table student drop constraint fk_student;
Table altered.
HR@ORA11GR2>
5）创建student表的外键，应用nativeplace的主键nid，并且设置为可以允许设置为延迟约束的立即验证状态
HR@ORA11GR2>alter table student add constraint fk_student foreign key (native_place) references nativeplace (nid) deferrable initially immediate;
Table altered.
HR@ORA11GR2>
6）设置当前事物为延迟约束验证
HR@ORA11GR2>set constraint fk_student deferred;
Constraint set.
HR@ORA11GR2>
7）再次对nativeplace表进行更新，nid=4依旧没有问题
HR@ORA11GR2>update nativeplace set nid=41 where nid=4;
1 row updated.
HR@ORA11GR2>
8）更新nativeplace表，将nid的3改为31，因为3这个值，student表的外键进行了引用，由于我们延迟验证，所以，也同样更新成功
HR@ORA11GR2>update nativeplace set nid=31 where nid=3;
1 row updated.
HR@ORA11GR2>
9）更新student表，将native_place=3的所有记录改为31，这样主、外键将保持一致
HR@ORA11GR2>update student set native_place=31 where native_place=3;
2 rows updated.
HR@ORA11GR2>
10）提交，事务结束，更新成功
HR@ORA11GR2>commit;
Commit complete.
HR@ORA11GR2>select * from student;
SNO SNAME NATIVE_PLACE
---- ---------- ------------
1 zhangsan 1
2 lisi 2
3 wangwu 31
4 zhaoliu 31
HR@ORA11GR2>
小结：
解决这种问题应该有很多办法，无疑，利用延迟约束的方式更为便利
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/685769/viewspace-743493/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
【延迟约束】功能的测试
请登录后发表评论
登录
全部评论
