oracle如何创建外键列,为什么子表外键列需要建立索引?（上）

外键是我们经常使用的数据库约束方式。它保证了子表上的特定索引列取值一定与父表列(主键列)相一致对应。而且保证在子表没有对应子记录的情况下，父表数据才能删除。应该说，外键是实现数据库完整性、消除潜在脏数据风险的重要手段。

在使用外键的时候，我们经常会要求在子表外键列上建立索引。这个“江湖定则”有什么样的原因呢？本篇我们一起来进行探讨。还是使用组合实验的方式来进行研究。

1、环境准备

我们在Oracle 11gR2版本上进行试验，构建初步的实验环境。

首先建立父子表关系。

SQL> select * from v$version;

BANNER

-----------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE11.2.0.1.0Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

--环境准备

SQL> create table master (id number, vname varchar2(10));

Table created

SQL> alter table master add constraint pk_master primary key (id);

Table altered

SQL> create table detail (did number, mid number, details varchar2(10));

Table created

SQL> alter table detail add constraint pk_detail primary key (did);

Table altered

SQL> alter table detail add constraint fk_detail_master_mid foreign key (mid) references master(id);

Table altered

上面建立了数据表master和detail，两个数据表分别以id和did作为主键。同时，detail的mid列作为与master表id列的外键关系，建立外键fk_detail_master_mid。

两个数据表对象的基础信息对应为：

SQL> select object_name,object_id from dba_objects where object_name in ('MASTER','DETAIL','IDX_DETAIL_MID');

OBJECT_NAMEOBJECT_ID

-------------------- ----------

DETAIL75191

MASTER75189

基础数据准备：

SQL> select * from master;

ID VNAME

---------- ----------

1 df

2 dfdf

3

4(后加入该条记录)

SQL> select * from detail;

DIDMID DETAILS

---------- ---------- ----------

11

21 dkff

32 fdkff

会话准备，作为并发操作实验，我们准备两个会话窗口。sid1和sid2，其中sid1为主操作会话窗口。sid1会话编号为135，sid2会话编号为18。另外，使用监控语句监控并发锁状态。

select sid,type,id1,id2,lmode,request,block from v$lock where sid in (135,18) and type not in ('AE','TO');

2、外键关联下，DML操作锁的特殊性

首先我们探讨一下Oracle条件下外键关联影响DML操作锁的情况。在一般情况下，Oracle使用的是行锁机制，保证进行DML操作的数据行不会被其他会话影响，也不会影响到其他数据行的操作，更不会影响到其他数据表的问题。

在没有外键的情况下，我们观察一下Oracle锁情况(额外实验，非Oracle11g环境)。

SQL> select sid from v$mystat where rownum<2;

SID

----------

144

SQL> insert into t select * from dba_objects;

50367 rows inserted

SQL> select sid, type ,id1, id2, lmode, request, block from v$lock where sid=144;

SID TYPEID1ID2LMODEREQUESTBLOCK

---------- ---- ---------- ---------- ---------- ---------- ----------

144 TM542060300

144 TX3277161068600

从这个案例中，可以看到Oracle进行DML操作的基本规范。在进行DML操作的时候，Oracle首先会尝试在数据表添加一个TM锁，级别是共享锁(lmode=3)。这个TM锁的id1参数为添加对象的object_id。这个锁的作用是保证在进行DML事务的时候，数据表不会被修改或者独占，体现在被添加3级锁以上级别的锁级别。这样，如果有其他会话要进行DML操作，在加入lmode=3锁的时候，是不会发生阻塞的。这样也就保证了对同一个数据表，是可以同时并发DML操作的。

其次，Oracle会独占使用数据行记录。就体现在添加的TX锁上，注意是lmode=6的级别锁。TX锁的参数信息，表示的是对应的事务段信息。

结论：在没有外键的作用下，Oracle进行的是最小粒度的锁控制。不会影响到其他数据表。

如果添加了外键，我们对数据表的DML操作后，将会有何种影响呢？

SQL> insert into master values (4,'dk');

1 row inserted

此时，监控结构。

SQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (135,18) and type not in ('AE','TO');

SID TYPEID1ID2LMODEREQUESTBLOCK

---------- ---- ---------- ---------- ---------- ---------- ----------

135 TM751890300

135 TM751910300

135 TX6553901000600

我们对主表进行操作，除了对主表的共享锁之外，还额外对子表(object_id=75191)进行加共享锁机制。

在主表操作上，delete主表操作不会带来对子表的连带锁操作。

SQL> delete master where id=3;

1 row deleted

SQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (135,18) and type not in ('AE','TO');

SID TYPEID1ID2LMODEREQUESTBLOCK

---------- ---- ---------- ---------- ---------- ---------- ----------

135 TM751890300

135 TX65564963600

update操作，也是不会影响到子表记录。

SQL> update master set vname='df' where id=3;

1 row updated

SQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (135,18) and type not in ('AE','TO');

SID TYPEID1ID2LMODEREQUESTBLOCK

---------- ---- ---------- ---------- ---------- ---------- ----------

135 TM751890300

135 TX262156999600

那么，我们对子表进行DML操作的时候，会连带影响到父表锁吗？下面进行DML操作实验。

SQL> insert into detail values (4,2,'d');

1 row inserted

SQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (135,18) and type not in ('AE','TO');

SID TYPEID1ID2LMODEREQUESTBLOCK

---------- ---- ---------- ---------- ---------- ---------- ----------

135 TM751890300

135 TM751910300

135 TX2621701001600

SQL> update detail set details='dkl' where did=3;

1 row updated

SQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (135,18) and type not in ('AE','TO');

SID TYPEID1ID2LMODEREQUESTBLOCK

---------- ---- ---------- ---------- ---------- ---------- ----------

135 TM751910300

135 TX3932361219600

SQL> delete detail where did=3;

1 row deleted

SQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (135,18) and type not in ('AE','TO');

SID TYPEID1ID2LMODEREQUESTBLOCK

---------- ---- ---------- ---------- ---------- ---------- ----------

135 TM751890300

135 TM751910300

135 TX3277131218600

子表操作的效果上，对子表进行insert和delete操作时候，也是会影响到父表锁结构的。

注意：我们上面的实验，是在子表无索引的情况下进行的。如果我们建立了索引结构，上面实验有什么不同的？

SQL> create index idx_detail_mid on detail(mid);

Index created

进行上述相同实验，由于篇幅原因，不加以累述，只是针对与无索引时候的差异进行阐述。

在对主表进行操作的时候，delete操作会引起连带的锁结构。

SQL> delete master where id=3;

1 row deleted

SQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (135,18) and type not in ('AE','TO');

SID TYPEID1ID2LMODEREQUESTBLOCK

---------- ---- ---------- ---------- ---------- ---------- ----------

135 TM751890300

135 TM751910300

135 TX5243191418600

对子表进行操作的时候，有无索引引起的锁结构相同。下表表示时候有连带锁效应。

外键列无索引

外键列有索引

主表DML操作

INSERT

Y

Y

UPDATE

N

N

DELETE

N

Y

子表DML操作

INSERT

Y

Y

UPDATE

N

N

DELETE

Y

Y

下面我们从并发会话的角度，看无索引状态下会带来的问题。

PS：系列总索引：