[事务相关] 事务与完整性约束

-- 查询某约束是哪种约束模式
select constraint_name,constraint_type,deferrable,deferred from user_constraints;

-- immediate 约束

-- 默认模式
-- SQL语句级别进行完整性检查
-- 如果一条语句(update)更新多条记录，会等到完全结束后才作检查


-- deferrable 约束与级联更新

-- 要延迟一个约束，必须先将其删除，再重新创建约束，这样才能把不可延迟的约束改变为可延迟约束

-- 父表
go@XE> create table p
(
    pk int primary key
)
/

Table created.

-- 子表
go@XE> create table c
(
    fk constraint c_fk references p(pk) deferrable initially immediate
)
/

Table created.

-- 测试数据
go@XE> insert into p values (1);

1 row created.

go@XE> insert into c values ( 1 );

1 row created.

go@XE> commit;

Commit complete.

go@XE> select * from p;
        PK
----------
         1

1 row selected.

go@XE> select * from c;
        FK
----------
         1

1 row selected.

-- 更新父表主键失败
-- 由于在immediate模式下，语句执行后进行完整性检查
go@XE> update p set pk = 2;
update p set pk = 2
*
ERROR at line 1:
ORA-02292: integrity constraint (GO.C_FK) violated - child record found

-- 设置外键约束延迟检查
go@XE> set constraint c_fk deferred;

Constraint set.

-- 由于延迟检查(commit时检查)，主键得以更新
go@XE> update p set pk = 2;

1 row updated.

go@XE> select * from p;
        PK
----------
         2

1 row selected.

go@XE> select * from c;
        FK
----------
         1

1 row selected.

-- 此时commit的话，由于未通过一致性检查，事务回滚到事务开始前状态
go@XE> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02292: integrity constraint (GO.C_FK) violated - child record found

go@XE> select * from p;
        PK
----------
         1

1 row selected.

go@XE> select * from c;
        FK
----------
         1

1 row selected.

-- 重新设置约束延迟检查
go@XE> set constraint c_fk deferred;

Constraint set.

go@XE> update p set pk = 2;

1 row updated.

-- 设置回immediate模式，设置失败，但事务不回滚
go@XE> set constraint c_fk immediate;
set constraint c_fk immediate
*
ERROR at line 1:
ORA-02291: integrity constraint (GO.C_FK) violated - parent key not found

go@XE> select * from p;
        PK
----------
         2

1 row selected.

go@XE> select * from c;
        FK
----------
         1

1 row selected.

-- 此时更新子表
go@XE> update c set fk = 2;

1 row updated.

-- 可以设置回immediate模式
go@XE> set constraint c_fk immediate;

Constraint set.

-- 提交成功，完成级联更新
go@XE> commit;

Commit complete.

go@XE> select * from p;
        PK
----------
         2

1 row selected.

go@XE> select * from c;
        FK
----------
         2

1 row selected.