主鍵與主鍵索引的關係

dawn009發表於2015-05-19
索引
在oracle中，我們建立一個主鍵，則同時自動建立了一個同名的唯一索引；刪除主鍵，則主鍵約束和對應的唯一索引都刪除了。這是我們經常見到的現象。
 
發出一個建立主鍵的sql，oracle其實執行了兩步：建立主鍵約束、建立/關聯 唯一索引。步驟是這樣的：
建立主鍵約束時，檢查該主鍵欄位上是否已經存在唯一索引。若不存在，則自動建立同名唯一索引；若存在，則直接建立主鍵約束，並將該約束和已經存在的唯一索引對應上。
刪除主鍵約束時，可以決定是否保留對應的索引；刪除唯一索引時，若存在對應的主鍵約束，則不能刪除。
 
總之，存在主鍵約束，則肯定存在與之對應的唯一索引，而存在唯一索引，不一定對應著有主鍵約束。
 
下面我們驗證一下：
 
SQL> create table test_pri(a number(1), b number(1));
 
表已建立。
 
--1.建立主鍵，則自動建立同名唯一索引
--1.1建立主鍵，主鍵約束和唯一索引同時建立
SQL> alter table test_pri add constraint pk_test_pri_a primary key(a);
 
表已更改。
 
SQL> select CONSTRAINT_NAME, TABLE_NAME, INDEX_NAME
  2  from user_constraints where table_name = 'TEST_PRI';
 
CONSTRAINT_NAME   TABLE_NAME   INDEX_NAME  
----------------- ------------ -------------
PK_TEST_PRI_A     TEST_PRI     PK_TEST_PRI_A
 
SQL> select INDEX_NAME, INDEX_TYPE, TABLE_NAME, UNIQUENESS
  2  from user_indexes where table_name = 'TEST_PRI';
 
INDEX_NAME       INDEX_TYPE   TABLE_NAME   UNIQUENES
---------------- ------------ ------------ ---------
PK_TEST_PRI_A    NORMAL       TEST_PRI     UNIQUE
 
--1.2 刪除主鍵，主鍵約束和對應的唯一索引都刪除了
SQL> alter table test_pri drop constraint pk_test_pri_a;
 
表已更改。
 
SQL> select CONSTRAINT_NAME, TABLE_NAME, INDEX_NAME
  2  from user_constraints where table_name = 'TEST_PRI';
 
未選定行
 
SQL> select INDEX_NAME, INDEX_TYPE, TABLE_NAME, UNIQUENESS
  2  from user_indexes where table_name = 'TEST_PRI';
 
未選定行
 
--1.3其實刪除主鍵時可以選擇保留索引的
SQL> alter table test_pri add constraint pk_test_pri_a primary key(a);
 
表已更改。
 
SQL> alter table test_pri drop constraint pk_test_pri_a keep index;
 
表已更改。
 
SQL> select CONSTRAINT_NAME, TABLE_NAME, INDEX_NAME
  2  from user_constraints where table_name = 'TEST_PRI';
 
未選定行
 
SQL> select INDEX_NAME, INDEX_TYPE, TABLE_NAME, UNIQUENESS
  2  from user_indexes where table_name = 'TEST_PRI';
 
INDEX_NAME      INDEX_TYPE  TABLE_NAME  UNIQUENES
--------------- ----------- ----------- ---------
PK_TEST_PRI_A   NORMAL      TEST_PRI    UNIQUE

 

--2.在存在唯一索引的列上建立主鍵，則只建立主鍵約束，同時將該約束與已有唯一索引關聯上（名稱可以不一致）
SQL> drop index pk_test_pri_a;
 
索引已丟棄。
 
--2.1 先建立唯一索引，再建立主鍵，名稱可以不一致
SQL> create unique index pk_test_pri_a on test_pri(a);
 
索引已建立。
 
SQL> alter table test_pri add constraint pk_test_pri primary key(a);
 
表已更改。
 
SQL> select INDEX_NAME, INDEX_TYPE, TABLE_NAME, UNIQUENESS
  2  from user_indexes where table_name = 'TEST_PRI';
 
INDEX_NAME      INDEX_TYPE  TABLE_NAME   UNIQUENES
--------------- ----------- ------------ ---------
PK_TEST_PRI_A   NORMAL      TEST_PRI     UNIQUE
 
SQL> select CONSTRAINT_NAME, TABLE_NAME, INDEX_NAME
  2  from user_constraints where table_name = 'TEST_PRI';
 
CONSTRAINT_NAME    TABLE_NAME   INDEX_NAME
------------------ ------------ -------------
PK_TEST_PRI        TEST_PRI     PK_TEST_PRI_A
 
--2.2 不可刪除存在主鍵約束的唯一索引
SQL> drop index PK_TEST_PRI_A;
drop index PK_TEST_PRI_A
           *
ERROR 位於第 1 行:
ORA-02429: 無法刪除用於強制唯一/主鍵的索引
 
--2.3雖然兩者名稱不一致，但也是關聯在一塊的：刪除約束，則對應的索引同時刪除
SQL> alter table test_pri drop constraint pk_test_pri;
 
表已更改。
 
SQL> select INDEX_NAME, INDEX_TYPE, TABLE_NAME, UNIQUENESS
  2  from user_indexes where table_name = 'TEST_PRI';
 
未選定行
 
SQL> select CONSTRAINT_NAME, TABLE_NAME, INDEX_NAME
  2  from user_constraints where table_name = 'TEST_PRI';
 
未選定行
 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29119536/viewspace-1661582/，如需轉載，請註明出處，否則將追究法律責任。

相關文章