【Oracle】【延迟约束】的应用
一、搭建环境
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
## 创建籍贯表 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'); ## 创建学生表 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的记录没有外键引用它,所以更新成功
1 2 3 4 5 |
HR@ORA11GR2>update nativeplace set nid=41 where nid=4; 1 row updated. HR@ORA11GR2> |
2)因为nid=3的记录有外键引用它所以失败
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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表的外键
1 2 3 4 5 |
HR@ORA11GR2> alter table student drop constraint fk_student; Table altered. HR@ORA11GR2> |
5)创建student表的外键,应用nativeplace的主键nid,并且设置为可以允许设置为延迟约束的立即验证状态
1 2 3 4 5 |
HR@ORA11GR2> alter table student add constraint fk_student foreign key (native_place) references nativeplace (nid) deferrable initially immediate; Table altered. HR@ORA11GR2> |
6)设置当前事物为延迟约束验证
1 2 3 4 5 |
HR@ORA11GR2> set constraint fk_student deferred; Constraint set. HR@ORA11GR2> |
7)再次对nativeplace表进行更新,nid=4依旧没有问题
1 2 3 4 5 |
HR@ORA11GR2> update nativeplace set nid=41 where nid=4; 1 row updated. HR@ORA11GR2> |
8)更新nativeplace表,将nid的3改为31,因为3这个值,student表的外键进行了引用,由于我们延迟验证,所以,也同样更新成功
1 2 3 4 5 |
HR@ORA11GR2> update nativeplace set nid=31 where nid=3; 1 row updated. HR@ORA11GR2> |
9)更新student表,将native_place=3的所有记录改为31,这样主、外键将保持一致
1 2 3 4 5 |
HR@ORA11GR2> update student set native_place=31 where native_place=3; 2 rows updated. HR@ORA11GR2> |
10)提交,事务结束,更新成功
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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> |
三、小结
解决这种问题应该有很多办法,无疑,利用延迟约束的方式更为便利