主键、唯一键与唯一索引的区别

一般，我们看到术语“索引”和“键”交换使用，但实际上这两个是不同的。索引是存储在数据库中的一个物理结构，键纯粹是一个逻辑概念。键代表创建来实施业务规则的完整性约束。索引和键的混淆通常是由于数据库使用索引来实施完整性约束。

 

接下来我们看看数据库中的主键约束、唯一键约束和唯一索引的区别。

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

通过实验，我们看出唯一索引与唯一键约束一样对列值非空不做要求。

 

如果我们让主键约束或者唯一键约束失效，Oracle自动创建的唯一索引是否会受到影响？

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 index_name, index_type, uniqueness from user_indexes;

 

INDEX_NAME                     INDEX_TYPE                  UNIQUENES

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

UK_TEST                        NORMAL                      UNIQUE

 

SQL> alter table test disable constraint uk_test;

 

 

Table altered.

 

SQL> select index_name, index_type, uniqueness from user_indexes;

 

no rows selected

当主键约束或者唯一键约束失效时，Oracle会删除隐式创建的唯一索引。

 

如果我们先创建唯一索引，再创建主键或者唯一键约束，情况又会怎样呢？

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> select index_name, index_type, uniqueness

  2  from user_indexes

  3  where table_owner = 'SCOTT'

  4  and table_name = 'TEST';

 -- 何问起 hovertree.com 

 

INDEX_NAME                     INDEX_TYPE                  UNIQUENES

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

IDX_TEST_ID                    NORMAL                      UNIQUE

 

SQL> alter table test add constraint uk_test unique (id);

 

Table altered.

 

SQL> select index_name, index_type, uniqueness

  2  from user_indexes

  3  where table_owner = 'SCOTT'

  4  and table_name = 'TEST';

 

INDEX_NAME                     INDEX_TYPE                  UNIQUENES

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

IDX_TEST_ID                    NORMAL                      UNIQUE

 

SQL> select constraint_name, constraint_type

  2  from user_constraints

  3  where table_name = 'TEST';

 

CONSTRAINT_NAME                C

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

UK_TEST                        U

 

SQL> alter table test disable constraint uk_test;

 

Table altered.

 

SQL> select constraint_name, constraint_type, status

  2  from user_constraints

  3  where table_name = 'TEST';

 

CONSTRAINT_NAME                C STATUS

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

UK_TEST                        U DISABLED

 

SQL> select index_name, index_type, uniqueness, status

  2  from user_indexes

  3  where table_owner = 'SCOTT'

  4  and table_name = 'TEST';

 

INDEX_NAME                     INDEX_TYPE                  UNIQUENES STATUS

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

IDX_TEST_ID                    NORMAL                      UNIQUE    VALID

 

实验结果表明，先创建的唯一索引不受约束失效的影响。

 

总结如下：

（1）主键约束和唯一键约束均会隐式创建同名的唯一索引，当主键约束或者唯一键约束失效时，隐式创建的唯一索引会被删除；

（2）主键约束要求列值非空，而唯一键约束和唯一索引不要求列值非空；

（3）相同字段序列不允许重复创建索引

http://www.cnblogs.com/roucheng/