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
　　通过实验，我们看出唯一索引与唯一键约束一样对列值非空不做要求。
　　
　　
　　如果我们让主键约束或者唯一键约束失效，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';
　　
　　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）相同字段序列不允许重复创建索引；