dbAnt

今天探索一下唯一性约束和唯一索引的关系，我们先抛出问题，这两者的效果是否一致的呢？

官方文档的解释：

Unique Constraints

A unique key constraint requires that every value in a column or set of columns be unique. No rows of a table may have duplicate values in a column (theunique key) or set of columns (the composite unique key) with a unique key constraint.

Note:

The term key refers only to the columns defined in the integrity constraint. Because the database enforces a unique constraint by implicitly creating or reusing an index on the key columns, the term unique key is sometimes incorrectly used as a synonym for unique key constraint or unique index.

 

Unique and Nonunique Indexes

Indexes can be unique or nonunique. Unique indexes guarantee that no two rows of a table have duplicate values in the key column or column. For example, no two employees can have the same employee ID. Thus, in a unique index, one rowid exists for each data value. The data in the leaf blocks is sorted only by key.

Nonunique indexes permit duplicates values in the indexed column or columns. For example, the first_name column of the employees table may contain multiple Mike values. For a nonunique index, the rowid is included in the key in sorted order, so nonunique indexes are sorted by the index key and rowid (ascending).

Oracle Database does not index table rows in which all key columns are null, except for bitmap indexes or when the cluster key column value is null.

 

【实验】

创建表T1并添加唯一性约束：

create table t1 (
id varchar2(20),
name varchar2(30),
constraints t1_id_uk unique(id));

创建表T2并创建唯一索引：

create table t2 (
id varchar2(20),
name varchar2(30));

create unique index ind_t2_id on t2(id);

查看两个表的索引，发现T1表自动创建了唯一索引：

select OWNER,TABLE_NAME,INDEX_NAME,INDEX_TYPE,UNIQUENESS from dba_indexes where table_name in ('T1','T2');

TABLE_NAME     INDEX_NAME    INDEX_TYPE      UNIQUENES
-------------- ------------- --------------- ---------
T1             T1_ID_UK      NORMAL          UNIQUE
T2             IND_T1_ID     NORMAL          UNIQUE

索引列也是ID列

select TABLE_OWNER,TABLE_NAME,INDEX_NAME,COLUMN_NAME,COLUMN_POSITION from dba_ind_columns where table_name in ('T1','T2');

TABLE_NAME      INDEX_NAME      COLUMN_NAME     COLUMN_POSITION
--------------- --------------- --------------- ---------------
T1              T1_ID_UK        ID                            1
T2              IND_T1_ID       ID                            1

检查两个表的约束，只有T1表存在唯一性约束：

select OWNER, TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE, STATUS from dba_constraints where table_name in ('T1','T2');

TABLE_NAME      CONSTRAINT_NAME      C STATUS
--------------- -------------------- - --------
T1              T1_ID_UK             U ENABLED

T1表插入数据：

insert into t1 values('A0001','TOM');
insert into t1 values('A0001','JACK');
commit;

kamner@TESTDB> insert into t1 values('A0001','JACK');
insert into t1 values('A0001','JACK')
*
ERROR at line 1:
<strong>ORA-00001: unique constraint (KAMNER.T1_ID_UK) violated</strong>

T2表出入数据

insert into t2 values('A0001','TOM');
insert into t2 values('A0001','JACK');
commit;

kamner@TESTDB> insert into t2 values('A0001','JACK');
insert into t2 values('A0001','JACK')
*
ERROR at line 1:
<strong>ORA-00001: unique constraint (KAMNER.IND_T1_ID) violated</strong>

可以看到，T1、T2表都报唯一性约束冲突的错误。

接着，我们插入NULL值。

kamner@TESTDB> insert into t1 values(null,'SCOTT');
1 row created.

kamner@TESTDB> commit;
Commit complete.

kamner@TESTDB> select * from t1;

ID                   NAME
-------------------- ------------------------------
A0001                TOM
                     SCOTT

kamner@TESTDB> insert into t1 values(null,'SCOTT');
1 row created.

kamner@TESTDB> insert into t1 values(null,'SCOTT');
1 row created.

kamner@TESTDB> commit;
Commit complete.

kamner@TESTDB> select * from t1;

ID                   NAME
-------------------- ------------------------------
A0001                TOM
                     SCOTT
                     SCOTT
                     SCOTT

我们看到，在Oracle中，每个NULL值都是不同的，因此他不受唯一索引的约束。这里给我们一点启发，在实际引用中，对于有唯一性要求的列，一般要同时添加非空约束。

综上，可以看出对一个表建唯一性约束和唯一索引的效果是一样的，而且可以看到，唯一性约束是通过唯一性索引来实现的。对于NULL值的情况，由于索引不会对NULL建索引页，因此更准确的说是在Oracle中，数据库没办法对NULL值进行比较，因此每个NULL值都看成是不一样的值。