[Oracle]談談主外鍵設計

主外鍵有兩大特點：

1）主鍵本身是一個唯一索引，保證主鍵所在列的唯一性；

2）外鍵列指必須在主表中的主鍵列有相應記錄。

外鍵上一定要建索引

我們知道，主鍵本身是一個唯一索引，外鍵是一個約束，默認情況下沒有索引，但在實際使用中強烈建議在外鍵上建索引，下面看兩個例子：

SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.

SQL> create table p (id number, name varchar2(30));

Table created.

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

Table altered.

SQL> create table f (id number, pid number, name varchar2(30));

Table created.

SQL> alter table f add constraint fk foreign key(pid) references p(id);

Table altered.

SQL> insert into p select rownum,table_name from dba_tables;

1206 rows created.

SQL> insert into f select rownum,mod(rownum,1000)+1,object_name from dba_objects;

14090 rows created.

SQL> commit;

Commit complete.

上面語句分別創建了兩個表，其中表p有主鍵，另一個表f有外鍵。但我們沒有在外鍵上創建索引，下面我們看下這兩表關聯的執行計劃和性能：

SQL> set autotrace traceonly
SQL> set line 1000
SQL> select p.id,p.name,f.name from p,f where p.id=f.pid and p.id=880;

14 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3936432439

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |    14 |   840 |    19   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |      |    14 |   840 |    19   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| P    |     1 |    30 |     0   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | PK   |     1 |       |     0   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS FULL          | F    |    14 |   420 |    19   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("P"."ID"=880)
   4 - filter("F"."PID"=880)

Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
        137  consistent gets
          0  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表進行了全表掃描，試想一下，如果p表不是返回1條記錄，而是返回多條記錄，那f表是不是要做多次的全部掃描？答案是肯定的，為了避免對f表的全表掃描，我們應該在外鍵上創建索引，如下所示：  

SQL> create index fk on f(pid);

Index created.

SQL> set autotrace traceonly
SQL> set line 1000
SQL> select p.id,p.name,f.name from p,f where p.id=f.pid and p.id=880;

14 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2077701003

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |    14 |   840 |    16   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |      |    14 |   840 |    16   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| P    |     1 |    30 |     0   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | PK   |     1 |       |     0   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| F    |    14 |   420 |    16   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | FK   |    14 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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.