很多人比較糾結的約束和索引的關係

最近有不少朋友留言或者微信問我一個問題，出乎我的意料，問題竟然都是很相似的，所以我統一答覆一下。
之前寫了一篇文章 一個清理和查詢都要兼顧的簡單方案，看來很多朋友還是很關心這個分割槽索引中的唯一性約束是怎麼建立的。我舉個例子，當然內容中會貫穿說到你們的疑問，而且很可能你麼對於約束和索引還是存在這一些誤解。
首先我們建立一個測試表，為了簡單起見，也沒有注意很多的命名規則了。
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
如果這個表非常大，這種操作的影響還是非常的大的，如果是分割槽表的全域性索引那麼影響也是全域性性的。簡單點說還是殺傷力太大。個人見解而已。