很多人比较纠结的约束和索引的关系
zz: jeanron100

最近有不少朋友留言或者微信问我一个问题，出乎我的意料，问

题竟然都是很相似的，所以我统一答复一下。
之前写了一篇文章 一个清理和查询都要兼顾的简单方案，看来

很多朋友还是很关心这个分区索引中的唯一性约束是怎么建立的

。我举个例子，当然内容中会贯穿说到你们的疑问，而且很可能

你么对于约束和索引还是存在这一些误解。
首先我们创建一个测试表，为了简单起见，也没有注意很多的命

名规则了。
create table test (id number,name varchar2(30))
partition by range(id)
(
partition p1 values less than(1),
partition p100 values less than (100),
partition p200 values less than(200)
);
我们就举unique约束的例子吧。
添加约束有很多种语法来实现。比如下面的三种
alter table test modify(id unique);
alter table test add constraint con_test_id_uq

unique(id);
alter table test add (constraint con_test_id_uq

unique(id));
首先我们来简单说明一个地方，就是generated的含义。
创建一个唯一性约束
alter table test modify(id unique);
这个时候查看约束的时候，发现约束名是系统自动生成的。
SQL> select constraint_name,table_name from

user_constraints where table_name='TEST';
CONSTRAINT_NAME TABLE_NAME
------------------------------ ----------------------

--------
SYS_C0031909 TEST
那么对应的index的情况如何呢。发现也是自动生成的，这个时

候字段generated的含义就是约束名是系统自动生成的。
SELECT

OWNER,CONSTRAINT_NAME,TABLE_NAME,GENERATED,INDEX_NAME

FROM USER_CONSTRAINTS WHERE TABLE_NAME='TEST';
OWNER CONSTRAINT_NAME

TABLE_NAME GENERATED

INDEX_NAME
------------------------------ ----------------------

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

------- ------------------------------
TEST SYS_C0031909

TEST GENERATED NAME

SYS_C0031909
好了，我们删除这个约束，继续测试，删除的同时会把约束也一

并删除。
SQL> ALTER TABLE TEST DROP CONSTRAINT SYS_C0031909

CASCADE;
Table altered.
这个时候我们创建一个约束，指定约束名。当然下面两种语法都

没有问题的。这就体现了Oracle语法解析器的强大之处。
alter table test add constraint con_test_id_uq

unique(id);
--alter table test add (constraint con_test_id_uq

unique(id));
当然创建出来约束之后，再来看看约束udev情况。
SQL> select constraint_name,table_name from

user_constraints where table_name='TEST';
CONSTRAINT_NAME TABLE_NAME
------------------------------ ----------------------

--------
CON_TEST_ID_UQ TEST
查看索引的情况，发现这个时候generated是user name,也就是

用户指定的名字。
SQL> SELECT

OWNER,CONSTRAINT_NAME,TABLE_NAME,GENERATED,INDEX_NAME

FROM USER_CONSTRAINTS WHERE TABLE_NAME='TEST';

OWNER CONSTRAINT_NAME

TABLE_NAME GENERATED

INDEX_NAME
------------------------------ ----------------------

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

------- ------------------------------
TEST CON_TEST_ID_UQ

TEST USER NAME

CON_TEST_ID_UQ
好了，到了大家关注的地方了，这个时候对这个分区表创建唯一

性约束，默认是会创建一个唯一性索引，但是这个索引是一个全

局索引。查看分区索引的数据字典视图，没有任何信息可以佐证

。
SQL> SELECT INDEX_NAME,PARTITION_NAME FROM

USER_IND_PARTITIONS WHERE INDEX_NAME='CON_TEST_ID_UQ';
no rows selected

好了，这个时候我来矫正一些知识点，首先来说上面两种创建约

束的方式，从规范的角度来说应该是需要使用第二种的，约束名

也是望名知义。小规则还是包含着大智慧。
然后对于创建Unique的本地约束，带有本地索引该怎么做呢，这

个时候需要先创建索引，然后创建约束绑定起来。
首先删除创建的测试约束，开始正式的创建。alter table

test drop constraint con_test_id_uq;
创建本地索引。
create unique index ind_test_uq on test(id) local;
这个时候还没有唯一性约束生成。
SQL> SELECT

OWNER,CONSTRAINT_NAME,TABLE_NAME,GENERATED,INDEX_NAME

FROM USER_CONSTRAINTS WHERE TABLE_NAME='TEST';
no rows selected
然后创建约束，这个时候仔细观察，其实会发现约束名和索引名

是不同的。也是各司其职。
alter table test add constraint con_test_uq unique(id)

using index ind_test_uq ;
SQL> SELECT

OWNER,CONSTRAINT_NAME,TABLE_NAME,GENERATED,INDEX_NAME

FROM USER_CONSTRAINTS WHERE TABLE_NAME='TEST';
OWNER CONSTRAINT_NAME

TABLE_NAME GENERATED

INDEX_NAME
-------------------- ------------------------------ -

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

----------------------
TEST CON_TEST_UQ

TEST USER NAME

IND_TEST_UQ
这个时候索引约束就映射起来了。如果直接删除索引，会有下面

的报错。
SQL> DROP INDEX IND_TEST_UQ;
DROP INDEX IND_TEST_UQ
*
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of

unique/primary key
对于这类的错误，尤其是生产环境中还是让人非常头痛的。
我们可以drop或者disable约束，然后删除即可。
SQL> ALTER TABLE TEST DROP CONSTRAINT CON_TEST_UQ;
Table altered.

SQL> DROP INDEX IND_TEST_UQ;
Index dropped.
如果你觉得这种方式有些特别，那么我们来看看tom怎么说。在

这一点上，其实Oracle也是建议做一个权衡，还是做到索引和约

束的分离。
Oracle recommends that unique indexes be created

explicitly, and not through enabling a unique

constraint on a table.
Alternatively, you can define UNIQUE integrity

constraints on the desired columns. Oracle enforces

UNIQUE integrity constraints by automatically defining

a unique index on the unique key. However, it is

advisable that any index that exists for query

performance, including unique indexes, be created

explicitly.

当然如果觉得上面的描述有些长，来两句简单的。
A unique constraint does not necessarily create an

index.
A unique constraint does not necessarily create a

UNIQUE index.

如果还觉得不好理解，还有方法。
a) unique did not create an index
b) unique does not need a unique index
如果还不理解，我们做个小的实验。
SQL> create table t( x int, y int );
Table created.

SQL> create index t_idx on t(x,y);
Index created.

SQL> alter table t add constraint t_unique unique(x);
Table altered.

SQL> select index_name,uniqueness,index_type from

user_indexes where table_name ='T';
INDEX_NAME UNIQUENES INDEX_TYPE
------------------------------ --------- ------------

---------------
T_IDX NONUNIQUE NORMAL
可以看到我们创建的压根不是唯一性索引，但是可以和普通索引

映射起来。
SQL> SELECT

OWNER,CONSTRAINT_NAME,TABLE_NAME,GENERATED,INDEX_NAME

FROM USER_CONSTRAINTS WHERE TABLE_NAME='T'
OWNER CONSTRAINT_NAME

TABLE_NAME GENERATED

INDEX_NAME
------------------------------ ----------------------

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

------------------------------
TEST T_UNIQUE

T USER NAME

T_IDX
明白了这一点，其实对于理解约束和索引的关系和关联还是很有

帮助的。
个人觉得为什么索引和约束要分开，有一个很大的原因就是因为

约束disable的情况下，索引会连带删除。
SQL> create table test2(id number,name varchar2(30));
Table created.
SQL> alter table test2 add constraint con_test2_id_uq

unique(id);
Table altered.
插入一些数据。
SQL> insert into test2 select object_id,object_name

from all_objects;
72888 rows created.
SQL> commit;
Commit complete.
索引和约束的关系如下：
SQL> select constraint_name,status,index_name from

user_constraints where table_name='TEST2';
CONSTRAINT_NAME STATUS INDEX_NAME
------------------------------ -------- -------------

-----------------
CON_TEST2_ID_UQ ENABLED

CON_TEST2_ID_UQ
把约束置为失效
SQL> alter table test2 disable constraint

con_test2_id_uq;
Table altered.
这个时候再次查看，索引已经没有了踪影，对应的段也不存在了

。
SQL> select constraint_name,status,index_name from

user_constraints where table_name='TEST2';
CONSTRAINT_NAME STATUS INDEX_NAME
------------------------------ -------- -------------

-----------------
CON_TEST2_ID_UQ DISABLED
如果这个表非常大，这种操作的影响还是非常的大的，如果是分

区表的全局索引那么影响也是全局性的。简单点说还是杀伤力太

大。个人见解而已。