主键及主键索引的关系及相互影响

    主键的定义：列或多列的集合，用于唯一的标识表中的一行。一个表上只允许有一个主键。
    我们在数据库中指定主键时，是通过主键约束来定义的。而创建主键约束时，又是需要有相应的索引来配合实现的。所以，本文的目的是总结创建主键约束时，采用不同的方法创建索引后，主键与该索引之间的关系及相互影响。
    为配合本文的描述和测试，首先创建如下测试表：


create table test (id number,c1 varchar2(8));


    一、 首先，我们来看最常用的创建主键约束及索引的方法：


alter table t1 add constraint pk_test_id primary key (id);
     创建完成后，检查相应约束和索引视图中的内容：


SQL> select constraint_name,constraint_type,table_name,status from user_constraints where table_name='TEST';
 
CONSTRAINT_NAME CO TABLE_NAME    STATUS
--------------- -- --------------- ----------------
PK_TEST_ID  P  TEST     ENABLED
      从上可见，我们已经创建了一个名为“PK_TEST_ID”的主键约束，其当前状态为有效状态。
 
SQL> select index_name,uniqueness,table_name,status from user_indexes where table_name='TEST';
 
INDEX_NAME  UNIQUENESS     TABLE_NAME       STATUS
--------------- ------------------ --------------- ----------------
PK_TEST_ID  UNIQUE         TEST        VALID
       从上可见，数据库同时自动为该主键约束创建了一个同名的唯一索引。
       如果这时，我们将主键约束关闭或者删除，又会是什么情况呢？
 
SQL> alter table test disable constraint pk_test_id;
 
Table altered.
 
SQL> select constraint_name,constraint_type,table_name,status from user_constraints where table_name='TEST';
 
CONSTRAINT_NAME CO TABLE_NAME    STATUS
--------------- -- --------------- ----------------
PK_TEST_ID  P  TEST     DISABLED
 
SQL> select index_name,uniqueness,table_name,status from user_indexes where table_name='TEST';
 
no rows selected
        可见，当我们把主键约束关闭后，其同名的索引被自动删除了。
        那如果我们不将主键约束关闭或者删除，而是将自动创建的同名索引关闭或删除，又会是什么情况呢？


SQL> alter table test enable constraint pk_test_id;
 
Table altered.
 
SQL> select constraint_name,constraint_type,table_name,status from user_constraints where table_name='TEST';
 
CONSTRAINT_NAME CO TABLE_NAME    STATUS
--------------- -- --------------- ----------------
PK_TEST_ID  P  TEST     ENABLED
 
SQL> select index_name,uniqueness,table_name,status from user_indexes where table_name='TEST';
 
INDEX_NAME  UNIQUENESS     TABLE_NAME       STATUS
--------------- ------------------ --------------- ----------------
PK_TEST_ID  UNIQUE         TEST        VALID
     首先，我们先恢复被关闭的主键约束，发现约束的状态已经恢复正常，而且同名的索引也被重建恢复了。现在我们将同名的索引关闭或删除。


SQL> alter index pk_test_id unusable;
 
Index altered.
 
SQL>  select constraint_name,constraint_type,table_name,status from user_constraints where table_name='TEST';
 
CONSTRAINT_NAME CO TABLE_NAME    STATUS
--------------- -- --------------- ----------------
PK_TEST_ID  P  TEST     ENABLED
 
SQL>  select index_name,uniqueness,table_name,status from user_indexes where table_name='TEST';
 
INDEX_NAME  UNIQUENESS     TABLE_NAME       STATUS
--------------- ------------------ --------------- ----------------
PK_TEST_ID  UNIQUE         TEST        UNUSABLE
      可见，当关闭索引后，除了索引的状态变为UNUSABLE外，主键的状态仍是正常状态。但若此时向表中插入数据，则会报错ORA-01502，如下所示：


SQL> insert into test values(1,'A');
insert into test values(1,'A')
*
ERROR at line 1:
ORA-01502: index 'U1.PK_TEST_ID' or partition of such index is in unusable state
      
      如果这时我们删除索引，也会报错，提示有约束正在使用该索引，而不允许删除。


SQL> drop index pk_test_id;
drop index pk_test_id
           *
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key
      从这两个现象，也证明主键约束是通过相应列上的索引来配合完成的。
      至此，我们对第一种创建约束和索引的方法做一个总结：当主键约束的索引为创建主键约束时数据库自动创建的话，则关闭或删除约束，会自动删除相应的索引；而关闭相应的索引，虽然不会影响主键的状态，但此时向表中插入数据会报错（删除数据，以及对主键列的更新也会报错）;而尝试删除该索引时，也会报错，而不允许删除该索引。


     二、创建主键约束时，指定相应的索引的方法。
     在创建主键约束时，我们也可以指定索引。方法如下：


SQL> drop table test purge;
 
Table dropped.
 
SQL> create table test (id number,c1 varchar2(8));
 
Table created.
 
SQL> alter table test add constraint pk_test_id primary key (id) using index (create index ind_test_id on test(id));
 
Table altered.
     前两条SQL是重建测试环境，以避免前面测试的影响。最后一条SQL则是在ID列上指定了主键约束，同时指定创建了在该列上的非唯一索引。
    我们通过相关约束和索引视图来查看一下：


SQL>  select constraint_name,constraint_type,table_name,status from user_constraints where table_name='TEST';
 
CONSTRAINT_NAME CO TABLE_NAME    STATUS
--------------- -- --------------- ----------------
PK_TEST_ID  P  TEST     ENABLED
 
SQL> select index_name,uniqueness,table_name,status from user_indexes where table_name='TEST';
 
INDEX_NAME  UNIQUENESS     TABLE_NAME       STATUS
--------------- ------------------ --------------- ----------------
IND_TEST_ID NONUNIQUE      TEST        VALID
     注意最后一条索引信息中索引的名称及唯一性与前面自动创建主键索引时的区别。这说明，非唯一索引，也可以用于配合完成主键约束。
     如前测试，我们尝试把主键约束关闭或删除，看看是什么情况？


SQL> alter table test disable constraint pk_test_id;
 
Table altered.
 
SQL> select constraint_name,constraint_type,table_name,status from user_constraints where table_name='TEST';
 
CONSTRAINT_NAME CO TABLE_NAME    STATUS
--------------- -- --------------- ----------------
PK_TEST_ID  P  TEST     DISABLED
 
SQL>  select index_name,uniqueness,table_name,status from user_indexes where table_name='TEST';
 
INDEX_NAME  UNIQUENESS     TABLE_NAME       STATUS
--------------- ------------------ --------------- ----------------
IND_TEST_ID NONUNIQUE      TEST        VALID
      可见，除了相应的主键约束失效外，索引并没有受到影响。这与前边自动创建主键索引的情况是不同的。
      接下来，我们再尝试关闭或删除相应的索引，是否会影响到相应的主键约束。


SQL> alter table test enable constraint pk_test_id;
 
Table altered.
 
SQL> alter index ind_test_id unusable;
 
Index altered.
 
SQL> select constraint_name,constraint_type,table_name,status from user_constraints where table_name='TEST';
 
CONSTRAINT_NAME CO TABLE_NAME    STATUS
--------------- -- --------------- ----------------
PK_TEST_ID  P  TEST     ENABLED
 
SQL> select index_name,uniqueness,table_name,status from user_indexes where table_name='TEST';
 
INDEX_NAME  UNIQUENESS     TABLE_NAME       STATUS
--------------- ------------------ --------------- ----------------
IND_TEST_ID NONUNIQUE      TEST        UNUSABLE
       可见，关闭索引并没有影响到相应主键约束的状态。但是否也如前面测试那样，不能插入数据呢？


SQL> insert into test values(1,'A');
insert into test values(1,'A')
*
ERROR at line 1:
ORA-01502: index 'U1.IND_TEST_ID' or partition of such index is in unusable state
      果然，当主键约束所依赖的索引不可用时，数据的插入会报错（删除数据，以及对主键列的更新也会报错）;而尝试删除该索引时，也会报错，而不允许删除该索引。这一点和前面测试的情况是相同的。


SQL> drop index ind_test_id;
drop index ind_test_id
           *
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key


    三、创建主键约束时，已经在相应的主键列上存在索引时的情况
    如果表TEST中已经存在了一个ID列上的索引，然后我再在ID列上创建主键约束，又是会是什么情况呢？
    和前面一样，我们先重置测试环境。
    


SQL> drop table test purge;
 
Table dropped.
 
SQL> create table test (id number,c1 varchar2(8));
 
Table created.
     然后，我们先创建一个ID列上的索引。


SQL> create index ind_test_id on test(id);
 
Index created.
     接着，我们在ID列上创建主键约束。
 


SQL> alter table test add constraint pk_test_id primary key (id);
 
Table altered.
     查看约束和索引的情况：


SQL> select constraint_name,constraint_type,table_name,status from user_constraints where table_name='TEST';
 
CONSTRAINT_NAME CO TABLE_NAME    STATUS
--------------- -- --------------- ----------------
PK_TEST_ID  P  TEST     ENABLED
 
SQL> select index_name,uniqueness,table_name,status from user_indexes where table_name='TEST';
 
INDEX_NAME  UNIQUENESS     TABLE_NAME       STATUS
--------------- ------------------ --------------- ----------------
IND_TEST_ID NONUNIQUE      TEST        VALID
     我们可以看到，这次创建主键约束时，自动选用了主键列上已经存在的索引，并没有像测试一中那样，去创建一个与主键同名的唯一索引。
     我们继续测试关闭或删除约束，是否会影响索引？
 


SQL> alter table test disable constraint pk_test_id;
 
Table altered.
 
SQL> select constraint_name,constraint_type,table_name,status from user_constraints where table_name='TEST';
 
CONSTRAINT_NAME CO TABLE_NAME    STATUS
--------------- -- --------------- ----------------
PK_TEST_ID  P  TEST     DISABLED
 
SQL> select index_name,uniqueness,table_name,status from user_indexes where table_name='TEST';
 
INDEX_NAME  UNIQUENESS     TABLE_NAME       STATUS
--------------- ------------------ --------------- ----------------
IND_TEST_ID NONUNIQUE      TEST        VALID
     发现索引并不受影响。
     继续测试关闭或删除索引对主键约束的影响。


SQL> alter table test enable constraint pk_test_id;
 
Table altered.
 
SQL>  alter index ind_test_id unusable;
 
Index altered.
 
SQL>  select constraint_name,constraint_type,table_name,status from user_constraints where table_name='TEST';
 
CONSTRAINT_NAME CO TABLE_NAME    STATUS
--------------- -- --------------- ----------------
PK_TEST_ID  P  TEST     ENABLED
 
SQL> select index_name,uniqueness,table_name,status from user_indexes where table_name='TEST';
 
INDEX_NAME  UNIQUENESS     TABLE_NAME       STATUS
--------------- ------------------ --------------- ----------------
IND_TEST_ID NONUNIQUE      TEST        UNUSABLE
 
SQL>  insert into test values(1,'A');
 insert into test values(1,'A')
*
ERROR at line 1:
ORA-01502: index 'U1.IND_TEST_ID' or partition of such index is in unusable state
 
 
SQL> drop index ind_test_id;
drop index ind_test_id
           *
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key
      和此前的测试完全一样，将主键约束所依赖的索引关闭并不会影响主键约束的状态，但修改数据（包括增删改操作，其中修改特指对主键列的修改）是不被允许的。同样，删除索引，也是不被允许的。


    至此，我们把前述三大测试总结一下：
    1、对于创建主键约束时自动生成的同名唯一性索引的情况：
       关闭或删除主键约束，相应的索引会被删除。
    2、对于创建主键约束时，使用了非自动创建的索引的情况（在创建主键约束的SQL中指定创建索引或创建主键约束时，主键列上已有索引。）：
       关闭或删除主键约束，相应的索引并不会被删除，索引状态也不受影响。
    3、在任何一种情况下，关闭主键约束所依赖的索引，都会引起数据插入、删除以及对主键列更新操作的报错。
    4、在任何一种情况下，删除主键约束所依赖的索引，是不被允许的。


注意：
     此外，有一个特例。即，当我们使用第二种方法，即在创建主键约束的同时，使用USING INDEX子句来创建了一个唯一索引时，比如：
alter table test add constraint pk_test_id primary key (id) using index (create unique index ind_test_id_uni on test(id));
      如果这时，我关闭或删除主键约束，则该索引会被自动删除。而当我们恢复主键约束时，数据库会自动创建一个与主键同名的唯一性索引。另外，当我们关闭手工创建的唯一索引或删除它时，情况与前述的内容是一样的。