Fw foriegn key and indices

Oracle中更新主外键时的一些注意事项
发表人:winkexp | 发表时间: 2005年一月18日, 23:58
这是很早前看tom的经典文章expert 101时的练习笔记，也许当时的理解并不深刻....现在不做开发了好多东西已经模糊了很多。


更新主外键时的一些注意事项
一、更新主外键

用scott用户打开两个窗口


1、外键无索引时，子表更新外键未提交，主表更新非子表引用的主键时被阻塞


会话1：


create table t1 (x int primary key);

insert into t1 values(1);

insert into t1 values(2);

insert into t1 values(3);

commit;

create table t2（y int references t1）；

insert into t2 values（1）；

commit;

update t2 set y=2 where y=1;


会话2：


update t1 set x=4 where x=3; //会话被阻塞


2、外键有索引时，子表更新外键未提交，主表更新非子表引用的主键时不会被阻塞


会话1：


create index t2_index on t2(y) ; //创建外键索引

update t2 set y=2 where y=1;


会话2：


update t1 set x=4 where x=3;

已更新 1 行；//可以正常更新


3、外键有无索引，对于子表更新外键未提交，主表更新相对应的主键无影响，更新主键的session都会被阻塞


会话1：


update t2 set y=2 where y=1;


会话2：


update t1 set x=4 where x=1; //更新子表已引用的


会话被阻塞。


会话1：


update t2 set y=2 where y=1;


会话2：


update t1 set x=4 where x=2 ; //更新子表将要引用的

会话被阻塞。――很好理解，主表要判断是否违反约束


二、更新子表非外键列未提交


1、外键无索引，更新主表已被外键引用的主键时，更新主键的session被阻塞


会话1：


create table t1 (x int primary key,x1 int);

insert into t1 values(1,1);

insert into t1 values(2,2);

insert into t1 values(3,3);

commit ;


create table t2(y int references t1,y1 int);

insert into t2 values(1,1);

commit ;

update t2 set y1=2 where y1=1;


会话2：



update t1 set x=4 where x=1; //更新外键引用的主键

会话被阻塞。


2、外键有索引，更新主表已被外键引用的主键时，更新主键的session不会被阻塞而报约束错误


会话1：


create index t2_index on t2(y);

update t2 set y1=2 where y1=1;


会话2：


update t1 set x=4 where x=1

*

ERROR 位于第 1 行:


ORA-02292: 违反完整约束条件 (SCOTT.SYS_C001607) - 已找到子记录日志


3、外键无索引，更新主表未被外键引用的主键时，更新主键的session被阻塞


会话1：


drop index t2_index；

update t2 set y1=2 where y1=1;


会话2:


update t1 set x=4 where x=2;

会话被阻塞。


4、外键有索引，更新主表未被外键引用的主键时，更新主键的session不会被阻塞


会话1：


create index t2_index on t2(y);

update t2 set y1=2 where y1=1;


会话2：


update t1 set x=4 where x=2;
已更新 1 行。



另外在一个主表有on delete cascade，子表没有外键索引时，对主表操作会级联到子表，子表将进行全表扫描。

总结：在需要更新主键的情况下，最好是创建子表的外键索引。