Oracle 唯一 約束（unique constraint） 與 索引（index） 關係說明

 

一. 官網對Unique Constraints說明

http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/datainte.htm#CNCPT1642

 

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

 

Note:

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

       --資料庫在建立unique constraint的同時，強制建立或者重用列上的索引。如果之前列上沒有索引，那麼強制建立的索引是unique index，如果列上已經存在索引，就重用之前的索引。

 

       Uniquekey constraints are appropriate for any column where duplicate values are notallowed. Unique constraints differ from primary keyconstraints, whose purpose is to identify each table row uniquely, andtypically contain values that have no significance other than being unique.Examples of unique keys include:

       （1）A customer phone number, where the primary key is the customernumber

       （2）A department name, where the primary key is the department number

 

       Asshown in Example2-1, a unique key constraint exists on the email column of the hr.employeestable. The relevant part of the statement is as follows:

CREATE TABLE employees    ( ...

    ,email          VARCHAR2(25)

       CONSTRAINT   emp_email_nn  NOT NULL ...

    ,CONSTRAINT     emp_email_uk  UNIQUE (email) ... );

 

       Theemp_email_uk constraint ensures that no two employees have the same emailaddress, as shown in Example5-1.

 

Example 5-1 Unique Constraint

SQL> SELECT employee_id, last_name,email FROM employees WHERE email = 'PFAY';

 

EMPLOYEE_ID LAST_NAME                 EMAIL

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

       202 Fay                       PFAY

 

SQL> INSERT INTO employees (employee_id,last_name, email, hire_date, job_id)   

 1  VALUES(999,'Fay','PFAY',SYSDATE,'ST_CLERK');

.

.

.

ERROR at line 1:

ORA-00001:unique constraint (HR.EMP_EMAIL_UK) violated

 

       Unless a NOT NULLconstraint is also defined, a null always satisfies a unique key constraint. Thus,columns with both unique key constraints and NOT NULL constraints are typical.This combination forces the user to enter values in the unique key andeliminates the possibility that new row data conflicts with existing row data.

 

Note:

       Because of the searchmechanism for unique key constraints on multiple columns, you cannot haveidentical values in the non-null columns of a partially null composite uniquekey constraint.

 

 

二. 相關測試2.1 測試unique index 和 uniqueconstraint

SYS@anqing2(rac2)> create table ut(idnumber,phone varchar2(15),name varchar2(15));

Table created.

 

SYS@anqing2(rac2)> insert into utvalues(1,'13888888888','dave');

1 row created.

SYS@anqing2(rac2)> insert into utvalues(1,'13888888888','dave');

1 row created.

SYS@anqing2(rac2)> insert into utvalues(2,'13899999999','dba');

1 row created.

SYS@anqing2(rac2)> commit;

Commit complete.

 

--在phone 欄位上，我們建立uniqueconstraint

SYS@anqing2(rac2)> alter table ut addconstraint uc_phone unique(phone);

alter table ut add constraint uc_phoneunique(phone)

                              *

ERROR at line 1:

ORA-02299: cannot validate (SYS.UC_PHONE) -duplicate keys found

--這裡報錯，因為我們在插入資料的時候，有重複值，先刪除掉重複值

 

SYS@anqing2(rac2)> select * from ut;

 

       ID PHONE           NAME

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

        1 13888888888     dave

        2 13899999999     dba

        1 13888888888     dave

 

SYS@anqing2(rac2)> delete from ut whererownum=1;

1 row deleted.

 

SYS@anqing2(rac2)> commit;

Commit complete.

 

SYS@anqing2(rac2)> select * from ut;

       ID PHONE           NAME

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

        2 13899999999     dba

        1 13888888888     dave

 

--唯一性限制式建立成功

SYS@anqing2(rac2)> alter table ut addconstraint uc_phone unique(phone);

Table altered.

 

--查看約束

SYS@anqing2(rac2)> selectconstraint_name,constraint_type,table_name,index_owner,index_name fromuser_constraints where table_name = 'UT';

 

CONSTRAINT_NAME C TABLE_NAME  INDEX_OWNER  INDEX_NAME

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

UC_PHONE        U UT            SYS           UC_PHONE

--Oracle 自動建立了索引並關聯到約束, 索引名和約束名是相同的。

 

--驗證下索引

SYS@anqing2(rac2)> selectindex_name,index_type,uniqueness,generated from user_indexes wheretable_name='UT';

 

INDEX_NAME    INDEX_TYPE    UNIQUENES GENERATED

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

UC_PHONE      NORMAL        UNIQUE    N

--我們並沒有建立索引，而是在建立unique constraint時，oracle 強制建立了uniqueindex。

 

--現在我們drop index 看看

SYS@anqing2(rac2)> drop index uc_phone;

drop index uc_phone

          *

ERROR at line 1:

ORA-02429: cannot drop index used forenforcement of unique/primary key

--這裡報錯，不能刪除unique/primary key 上的索引。在這種情況下，我們只有先刪除約束。

 

SYS@anqing2(rac2)> alter table ut dropconstraint uc_phone;

Table altered.

 

SYS@anqing2(rac2)> drop index uc_phone;

drop index uc_phone

          *

ERROR at line 1:

ORA-01418: specified index does not exist

--再次drop 索引時，提示索引已經不存在，說明已經在刪除約束的同時，把索引刪掉了。

 

SYS@anqing2(rac2)> selectconstraint_name,constraint_type,table_name,index_owner,index_name fromuser_constraints where table_name = 'UT';

no rows selected

 

SYS@anqing2(rac2)> selectindex_name,index_type,uniqueness,generated from user_indexes wheretable_name='UT';

no rows selected

 

結論：

       當約束列上沒有索引時，在建立unique constraint 時，oracle 會自動建立unique index，並且該索引不能刪除，當刪除unique constraint 時，unique index 會自動刪除。

 

 

2.2 測試unique constraint 和non-unique index

 

--現在欄位phone上建立B-Tree索引

SYS@anqing2(rac2)> create indexidx_ut_phone on ut(phone);

Index created.

 

--查看索引

SYS@anqing2(rac2)> selectindex_name,index_type,uniqueness,generated from user_indexes wheretable_name='UT';

INDEX_NAME    INDEX_TYPE    UNIQUENES GENERATED

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

IDX_UT_PHONE  NORMAL       NONUNIQUE N

 

--建立unique constraint

SYS@anqing2(rac2)>  alter table ut add constraint uc_phoneunique(phone);

Table altered.

 

--查看約束和索引資訊

SYS@anqing2(rac2)> selectconstraint_name,constraint_type,table_name,index_owner,index_name fromuser_constraints where table_name = 'UT';

 

CONSTRAINT_NAME C TABLE_NAME   INDEX_OWNER  INDEX_NAME

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

UC_PHONE        U UT            SYS           IDX_UT_PHONE

--這裡重用了已經存在的索引

 

SYS@anqing2(rac2)> selectindex_name,index_type,uniqueness,generated from user_indexes wheretable_name='UT';

INDEX_NAME    INDEX_TYPE    UNIQUENES GENERATED

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

IDX_UT_PHONE  NORMAL       NONUNIQUE N

 

--刪除索引

SYS@anqing2(rac2)> drop indexIDX_UT_PHONE;

drop index IDX_UT_PHONE

          *

ERROR at line 1:

ORA-02429: cannot drop index used forenforcement of unique/primary key

--這個提示和之前的一樣，我們先刪除約束，在來查看

 

SYS@anqing2(rac2)> alter table ut dropconstraint uc_phone;

Table altered.

 

SYS@anqing2(rac2)> select constraint_name,constraint_type,table_name,index_owner,index_namefrom user_constraints where table_name = 'UT';

no rows selected

--這裡約束已經刪除掉了。

 

SYS@anqing2(rac2)> selectindex_name,index_type,uniqueness,generated from user_indexes wheretable_name='UT';

INDEX_NAME    INDEX_TYPE    UNIQUENES GENERATED

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

IDX_UT_PHONE  NORMAL       NONUNIQUE N

--但是我們的索引並在刪除約束時刪除掉

 

--在手工刪除索引，成功

SYS@anqing2(rac2)> drop indexIDX_UT_PHONE;

Index dropped.

 

SYS@anqing2(rac2)> selectindex_name,index_type,uniqueness,generated from user_indexes wheretable_name='UT';

no rows selected

 

--重新把約束和索引加上，然後一次刪除

SYS@anqing2(rac2)> create indexidx_ut_phone on ut(phone);

Index created.

SYS@anqing2(rac2)> alter table ut addconstraint uc_phone unique(phone);

Table altered.

 

SYS@anqing2(rac2)> selectconstraint_name,constraint_type,table_name,index_owner,index_name fromuser_constraints where table_name = 'UT';

CONSTRAINT_NAME C TABLE_NAME    INDEX_OWNER   INDEX_NAME

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

UC_PHONE        U UT            SYS           IDX_UT_PHONE

 

SYS@anqing2(rac2)> selectindex_name,index_type,uniqueness,generated from user_indexes wheretable_name='UT';

INDEX_NAME    INDEX_TYPE    UNIQUENES GENERATED

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

IDX_UT_PHONE  NORMAL       NONUNIQUE N

 

SYS@anqing2(rac2)> alter table ut drop constraint uc_phone drop index;

Table altered.

SYS@anqing2(rac2)> selectconstraint_name,constraint_type,table_name,index_owner,index_name fromuser_constraints where table_name = 'UT';

no rows selected

SYS@anqing2(rac2)> selectindex_name,index_type,uniqueness,generated from user_indexes wheretable_name='UT';

no rows selected

--索引和約束一次刪除

 

 

小結：

       當我們的列上有索引時，在建立unique constraint時，Oracle 會重用之前的索引，並且不會改變索引的類型，在第一個測試裡，Oracle 自動建立的索引是unique index。

       當我們刪除約束時，關聯的索引不會自動刪除。 這個問題的MOS 上有說明。 參考MOS [ID309821.1]。

       我們可以分兩步，先刪除約束，在刪除索引。 MOS 提供了方法，就是在刪除約束時，加上drop index，這樣就能一次搞定。

       SQL>altertable ut drop constraint uc_phone drop index;

 

 

 

 

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

Blog： http://blog.csdn.net/tianlesoftware

Email: dvd.dba@gmail.com

DBA1 群：62697716(滿);   DBA2 群：62697977(滿)  DBA3 群：62697850(滿)  

DBA 超級群：63306533(滿);  DBA4 群： 83829929  DBA5群： 142216823   

DBA6 群：158654907  聊天 群：40132017   聊天2群：69087192

--加群需要在備忘說明Oracle資料表空間和資料檔案的關係，否則拒絕申請