Oracle 主鍵、唯一鍵與唯一索引的區別

一般，我們看到術語“索引”和“鍵”交換使用，但實際上這兩個是不同的。索引是儲存在資料庫中的一個物理結構，鍵純粹是一個邏輯概念。鍵代表建立來實施商務規則的完整性條件約束。索引和鍵的混淆通常是由於資料庫使用索引來實施完整性條件約束。

推薦閱讀：

Oracle主鍵約束自動建立索引問題

Oracle 10g刪除主鍵約束後無法刪除唯一約束索引問題的類比與分析

介紹Oracle Virtual Index虛擬索引

Oracle中檢查是否需要重構索引

Oracle case when索引 空值索引 位元影像索引

Oracle分析表和索引

Oracle主鍵約束自動建立索引問題

 
接下來我們看看資料庫中的主鍵約束、唯一鍵約束和唯一索引的區別。
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
 
SQL> create table test (         
  2  id int,
  3  name varchar2(20),
  4  constraint pk_test primary key(id))
  5  tablespace users;
Table created.
 
SQL> select constraint_name, constraint_type from user_constraints;
CONSTRAINT_NAME                C
------------------------------ -
PK_TEST                        P
 
在test表中，我們指定了ID列作為主鍵，Oracle資料庫會自動建立一個同名的唯一索引：
SQL> select index_name, index_type, uniqueness, tablespace_name
  2  from user_indexes
  3  where table_owner='SCOTT'
  4  and table_name = 'TEST';
INDEX_NAME          INDEX_TYPE          UNIQUENES TABLESPACE_NAME
-------------------- -------------------- --------- ------------------------------
PK_TEST              NORMAL              UNIQUE    USERS
 
此時，如果我們再試圖在ID列上建立一個唯一索引，Oracle會報錯，因為該列上已經存在一個唯一索引：
SQL> create unique index idx_test_uk on test(id);
create unique index idx_test_uk on test(id)
                                        *
ERROR at line 1:
ORA-01408: such column list already indexed
 
即使建立非唯一索引也不行：
SQL> create index idx_test_id on test(id);
create index idx_test_id on test(id)
                                *
ERROR at line 1:
ORA-01408: such column list already indexed
 
那麼唯一鍵約束的情況是怎樣的呢？
SQL> drop table test purge;
Table dropped.
 
SQL> create table test(
  2  id int,
  3  name varchar2(20),
  4  constraint uk_test unique(id));
Table created.
 
SQL> select constraint_name, constraint_type from user_constraints;
CONSTRAINT_NAME                C
------------------------------ -
UK_TEST                        U
 
查看此時的索引情況：
SQL> select index_name, index_type, uniqueness, tablespace_name
  2  from user_indexes
  3  where table_owner='SCOTT'
  4  and table_name = 'TEST';
INDEX_NAME          INDEX_TYPE          UNIQUENES TABLESPACE_NAME
-------------------- -------------------- --------- ------------------------------
UK_TEST              NORMAL              UNIQUE    USERS
Oracle同樣自動建立了一個同名的唯一索引，而且也不允許再在此列上建立唯一索引或非唯一索引。
 
我們知道，主鍵約束要求列值非空（NOT NULL），那麼唯一鍵約束是否也要求非空呢？
SQL> insert into test values(1, 'Sally');
1 row created.
 
SQL> insert into test values(null, 'Tony');
1 row created.
 
SQL> insert into test values(null, 'Jack');
1 row created.
 
SQL> select * from test;
        ID NAME
---------- --------------------
        1 Sally
          Tony
          Jack
 
從實驗結果來看，唯一鍵約束並沒有非空要求。
 
接下來我們看看唯一索引對列值的非空要求有什麼不同。
 
SQL> drop table test purge;
Table dropped.
 
SQL> create table test(
  2  id int,
  3  name varchar2(20));
Table created.
 
SQL> create unique index idx_test_id on test (id);
Index created.
 
SQL> insert into test values(1, 'Sally');
1 row created.
 
SQL> insert into test values(null, 'Tony');
1 row created.
 
SQL> insert into test values(null, 'Jack');
1 row created.
 
SQL> select * from test;
        ID NAME
---------- --------------------
        1 Sally
          Tony
          Jack
 
通過實驗，我們看出唯一索引與唯一鍵約束一樣對列值非空不做要求。

 

繼續閱讀本文的精彩內容請看第2頁：

1 2 3 下一頁