从专家那里要来得图片
主外键
create table t3 (id number primary key,name varchar2(20),product_id number);
create table t2 (id number primary key,name varchar2(20));
alter table t3 add constraint FK_PRODUCTSTAT_PRODUCTID foreign key (PRODUCT_id) references t2 (ID);
SQL> insert into t2 values(1,'a');
1 row inserted
SQL> insert into t2 values(2,'c');
1 row inserted
SQL> insert into t2 values(3,'b');
1 row inserted
SQL> commit;
Commit complete
session 1执行如下操作:
SQL> select * from t2;
ID NAME
---------- --------------------
1 a
2 c
3 b
SQL> select * from t3;
ID NAME PRODUCT_ID
---------- -------------------- ---------- --可以看到,这时t3表有任何记录
SQL> insert into t2 values(4,'cc'); --对父表执行一条插入
1 row inserted、
session2 t3表执行一条插入操作,如下
insert into t3 values(1,'tt',4);
,确实发生了锁等待
案例2 主键表delete大量语句,外键没有索引,导致外检表处理外键列全表扫描