oracle中约束和索引的前后关系

增加唯一性约束会自动创建唯一性索引：

SQL> create table t (c1 number, c2 varchar2(30));

Table created.

SQL> alter table t add constraint uk_t_c1 unique (c1);

Table altered.

SQL> select index_name from user_indexes where table_name = 'T';

INDEX_NAME
------------------------------
UK_T_C1

SQL> select constraint_name from user_constraints where table_name = 'T';

CONSTRAINT_NAME
------------------------------
UK_T_C1

删除约束也会自动把相应的所以删除：

SQL> alter table t drop constraint uk_t_c1;

Table altered.

SQL> select index_name from user_indexes where table_name = 'T';

no rows selected

SQL> select constraint_name from user_constraints where table_name = 'T';

no rows selected

除非删除约束时加上keep index以保留索引：

SQL> alter table t add constraint uk_t_c1 unique (c1);

Table altered.

SQL> alter table t drop constraint uk_t_c1 keep index;

Table altered.

SQL> select index_name from user_indexes where table_name = 'T';

INDEX_NAME
------------------------------
UK_T_C1

SQL> select constraint_name from user_constraints where table_name = 'T';

no rows selected

SQL> drop index uk_t_c1;

Index dropped.

SQL> select index_name from user_indexes where table_name = 'T';

no rows selected

但是如果先建立了索引再建约束：

SQL> create unique index uk_t_c1 on t (c1);

Index created.

SQL> select index_name from user_indexes where table_name = 'T';

INDEX_NAME
------------------------------
UK_T_C1

SQL> alter table t add constraint uk_t_c1 unique (c1);

Table altered.

这时候删除约束的时候就不会把对应的索引删除：

SQL> alter table t drop constraint uk_t_c1;

Table altered.

SQL> select index_name from user_indexes where table_name = 'T';

INDEX_NAME
------------------------------
UK_T_C1

SQL>

如果想在删除约束时保留索引，建议还是最好明确使用keep index，以避免意想不到的问题发生。

--End--