oracle主键和唯一索引,Oracle 主键、唯一键与唯一索引的区别

如果我们让主键约束或者唯一键约束失效，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

SQL> alter table test disable constraint uk_test;

Table altered.

SQL> select index_name, index_type, uniqueness from user_indexes;

INDEX_NAME INDEX_TYPE UNIQUENES

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

UK_TEST NORMAL UNIQUE

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

=====================================================

Using Nonunique Indexes to Enforce Uniqueness

You can use an existing nonunique index on a table to enforce uniqueness, either for UNIQUE constraints or the unique aspect of a PRIMARY KEY constraint. The advantage of this approach is that the index remains available and valid when the constraint is disabled. Therefore, enabling a disabled UNIQUE or PRIMARY KEY constraint does not require rebuilding the unique index associated with the constraint. This can yield significant time savings on enable operations for large tables.

Using a nonunique index to enforce uniqueness also lets you eliminate redundant indexes. You do not need a unique index on a primary key column if that column is included as the prefix of a composite index. You can use the existing index to enable and enforce the constraint. You also save significant space by not duplicating the index. However, if the existing index is partitioned, then the partitioning key of the index must also be a subset of the UNIQUE key; otherwise, Oracle Database creates an additional unique index to enforce the constraint.

=====================================================

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

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