[Oracle]谈谈主外键设计 - 数据库编程

------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("P"."ID"=880) 5 - access("F"."PID"=880) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 9 recursive calls 0 db block gets 90 consistent gets 1 physical reads 0 redo size 1094 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 14 rows processed 从上面的执行计划我们可以知道，对f表不进行全表扫描了，而是走索引。

除了上述原因之外，外键建索引的另一个好处是可以避免锁的争用，看下面这个例子：

首先，我们把外键上的索引删除，然后在f表上随便删除某条记录，但不提交。

SQL> drop index fk;

Index dropped.

SQL> delete from f where id=2;

1 row deleted.

接着，我们打开另一个会话，在p表上随便删除一条数据，发现被阻塞了：

SQL> delete from p where id=2000;

以下是锁的情况：

SQL> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by sid, type;

       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
       254 TM      14356          0          3          0          0
       254 TM      14359          0          3          0          1
       254 TX     655369       1307          6          0          0
      1388 TM      14359          0          0          4          0
      1388 TM      14356          0          3          0          0

在这里我们惊奇的发现，p这个主键所在的表，居然因为外键所在的f表随意删除一条记录，导致p表被完全锁住，无法做任何的DML操作，这是多么可怕啊！

更改为主键的简便方法

如果今天生产系统有一张大表的某字段符合主键的条件，没有重复记录，但却只是一个普通索引，要更改为主键，该如何操作呢？

因为建主键的操作其实就是建了一个唯一性索引，再增加一个约束，所以我们只要增加一个约束就可以了：

SQL> create index normal_idx on p(id);

Index created.

SQL> alter table p add constraint pk primary key(id);             

Table altered.

[Oracle]谈谈主外键设计(二)

更改为主键的简便方法