[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.