1.主键列上建立索引
这里我改变了书中介绍的顺序,首先介绍建表同时建立主键约束的情况,这种在测试中最常见。
SQL> create table t0115 (id int primary key,name varchar(20));
Table created.
SQL> select constraint_name,index_name from dba_constraints where table_name='T0115';
CONSTRAINT_NAME INDEX_NAME
------------------------------ ------------------------------
SYS_C0010933 SYS_C0010933
建立主键约束时如果不定义约束名称,数据库会为约束和其建立的索引自动分配一个名称,索引继承所在表的表空间存储参数。如果需要指定约束和索引的名称,需要建表时指定primary key名称。
SQL> create table t0115 (id int constraint t0115_pk primary key,name varchar(20));
Table created.
SQL> select constraint_name,index_name from dba_constraints where table_name='T0115';
CONSTRAINT_NAME INDEX_NAME
------------------------------ ------------------------------
T0115_PK T0115_PK
这种写法有一个问题,只能定义一列主键,如果需要建立复合主键采用out-of-line写法。
SQL> create table t0115 (id int,name varchar(20),constraint t0115_pk primary key(id,name));
Table created.
SQL> select table_name,column_name from dba_cons_columns a,(select constraint_name from dba_constraints where table_name='T0115' and constraint_type='P')b where a.constraint_name=b.constraint_name;
TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
T0115
ID
T0115
NAME
第二种方式是将建表和建立主键约束分开来,这是生产环境中通常采用的做法。
SQL> create table t0115 (id int,name varchar(20));
Table created.
SQL> alter table t0115 add constraint t0115_pk primary key (id);
Table altered.
第三种方式是将建立索引和建立主键约束分开来,这种方式个人认为并不具备太大意义,书中介绍了两种场景,①需要删除或禁用约束但保留索引,这个需求可以通过alter table tab_name drop constraint con_name keep index实现;②主键定义列与索引列不同,这种情况非常少见,因为手动建立的唯一索引与主键约束建立的索引并不冲突。
SQL> create table t0115 (id int,name varchar(20));
SQL> create unique index t0115_index on t0115(id);
SQL> alter table t0115 add constraint t0115_pk primary key (id);
2.唯一键列上建立索引
唯一约束和主键约束在一些功能上是类似的,比如都可以检查键值的唯一性(主键约束可以看成是唯一约束和非空约束的结合),下面的表简单说明了两者的异同。
# | 主键约束 | 唯一约束 |
---|---|---|
共同点 | 键值唯一性,建立约束同时建立唯一索引 | 同 |
区别 | 不允许NULL | 可以存在NULL值 |
区别 | 唯一主键 | 可以同时存在多个唯一约束 |
主键列上建立索引的三种方法,同样适用于唯一约束,这里不再详述。主要介绍一下单独建立唯一索引(但不建立唯一约束)的问题,这个问题解释了唯一索引和唯一约束的区别,这也是大家刚刚接触ORACLE数据库经常会有的疑问。通常情况下,唯一索引(并不依赖唯一约束)和唯一约束都用来检查列数据的唯一性,但对于有外键引用的数据列,只能使用唯一约束而不能用唯一索引。
SQL> create table t0115 (id int primary key,name varchar(20) constraint t0115_uk unique);
Table created.
SQL> create table t0116 (id int primary key,name varchar(20),constraint t0116_fk foreign key(name) references t0115(name));
Table created.
SQL> drop table t0116;
Table dropped.
SQL> alter table t0115 drop constraint t0115_uk keep index;
Table altered.
SQL> select index_name from dba_indexes where table_name='T0115';
INDEX_NAME
------------------------------
SYS_C0010942
T0115_UK
SQL> create table t0116 (id int primary key,name varchar(20),constraint t0116_fk foreign key(name) references t0115(name));
create table t0116 (id int primary key,name varchar(20),constraint t0116_fk foreign key(name) references t0115(name))
*
ERROR at line 1:
ORA-02270: no matching unique or primary key for this column-list
可以看到在表t0115 name列建立唯一约束,表t0116可以引用父表的对应字段,删除约束只保留索引后,建立外键报错“父表上没有匹配的主键或唯一键”。
唯一索引另一个值得关注的问题是违反唯一性的报错信息:
SQL> insert into t0115 values (1,'SXD');
1 row created.
SQL> insert into t0115 values (2,'SXD');
insert into t0115 values (2,'SXD')
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.T0115_UK) violated
大名鼎鼎的ORA-00001违反唯一约束检查,但这个报错信息很容易引起困惑,对于没有唯一约束的情况,根据TEST.T0115_UK检查dba/user_ constraint视图是查不到信息的,这时候需要去dba/user_ indexes查看。
SQL> select constraint_name from dba_constraints where constraint_name='T0115_UK';
no rows selected
SQL> select index_name from dba_indexes where index_name='T0115_UK';
INDEX_NAME
------------------------------
T0115_UK
3.外键列上建立索引
和主键/唯一约束自带索引不同,外键列的索引需要手动建立,建立的方法也很简单,主要关注一下外键索引的作用:①改善使用外键连接父表与子表的查询性能,这个很好理解,索引可能帮助在子表中更快定位记录;②避免子表插入/删除数据时父表的全表锁定。对于第二点,做个实验验证一下。
SQL> create table t0115 (id int primary key,name varchar(20) constraint t0115_uk unique);
create table t0116 (id int primary key,name varchar(20),constraint t0116_fk foreign key(id) references t0115(id));
insert into t0115 values (1,'SXD');
insert into t0115 values (2,'SYK');
insert into t0116 values (1,'SXD');
insert into t0116 values (2,'SYK');
Table created.
SQL>
Table created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL> insert into t0116 values (2,'SYK');
insert into t0116 values (2,'SYK');insert into t0116 values (2,'SYK')
*
ERROR at line 1:
ORA-00911: invalid character
SQL> insert into t0116 values (2,'SYK');
1 row created.
SQL> commit;
Commit complete.
SQL> delete from t0116 where id=1;
1 row deleted.
SQL> delete from t0115 where id=2;
delete from t0115 where id=2
*
ERROR at line 1:
ORA-02292: integrity constraint (TEST.T0116_FK) violated - child record found
当子表删除数据行,父表(非关联)数据行也被锁定。
SQL> select b.sid,b.sql_id from v$transaction a,v$session b where a.ADDR=b.TADDR;
SID SQL_ID
---------- -------------
140 d8ahshuqcs3n8
SQL> select sql_text from v$sql where sql_id='3rq7g6tbr3dhj';
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
delete from t0115 where id=2
SQL> select * from v$lock where sid=140 and type in ('TM','TX');
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00007FAD1C976230 00007FAD1C976290 140 TM 73981 0 3 0 4137 0
00007FAD1C976230 00007FAD1C976290 140 TM 73984 0 3 0 4137 0
000000008F0EF648 000000008F0EF6C0 140 TX 65536 749 6 0 4137 0
删除父表数据行的事务产生等待,此时140会话持有3个锁,t0115/t0116表锁和t0115行锁。这里有两点需要说明:
1.外键引起的父表锁定不是表级别的,TM行共享(RS)锁允许其他事务插入、更新、删除除TX锁定外的行。
2. 子表插入、更新数据行,父表关联数据行将被锁定;子表删除数据行,父表关联数据行依然可以操作。