ORA-01502, constraint, unique index and skip_unusable_indexes=true

This example shows the difference between unique indexes and non-unique indexes used to enforce primary key/unique constraints.

beside this, the only way to enable primary key/unique constraint to deferred is using non-unique index.

SQL> define _editor=vi
SQL> create table emp (
  2  empno number(5),
  3  ssn number(5),
  4  constraint emp_pk primary key(empno) using index
  5        (create unique index emp_pk on emp(empno) tablespace users),
  6  constraint emp_unique_ssn unique(ssn) using index
  7        (create unique index emp_u1 on emp(ssn) tablespace users)
  8*   )
  9  /

Table created.

SQL> alter index emp_pk unusable;

Index altered.

SQL> 
SQL> 

SQL>  insert into emp (empno) select employee_id from hr.employees;
 insert into emp (empno) select employee_id from hr.employees
*
ERROR at line 1:
ORA-01502: index 'DONGHUA.EMP_PK' or partition of such index is in unusable
state


SQL> alter table emp disable constraint emp_pk keep index;

Table altered.

SQL> insert into emp (empno) select employee_id from hr.employees;
insert into emp (empno) select employee_id from hr.employees
*
ERROR at line 1:
ORA-01502: index 'DONGHUA.EMP_PK' or partition of such index is in unusable
state


SQL> 
SQL> show parameter index

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
optimizer_index_caching       integer  0
optimizer_index_cost_adj      integer  100
optimizer_use_invisible_indexes      boolean  FALSE
skip_unusable_indexes       boolean  TRUE
SQL> drop index emp_pk;

Index dropped.

SQL> create index emp_pk on emp(empno) unusable;

Index created.

SQL> insert into emp (empno) select employee_id from hr.employees;

107 rows created.

SSQL> commit;

Commit complete.

SQL> alter table emp enable constraint emp_pk;
alter table emp enable constraint emp_pk
*
ERROR at line 1:
ORA-14063: Unusable index exists on unique/primary constraint key


SQL> alter index emp_pk rebuild;

Index altered.

SQL> alter table emp enable constraint emp_pk;

Table altered.

SQL> truncate table emp;

Table truncated.

SQL> alter index emp_pk unusable;

Index altered.

SQL> insert into emp (empno) select employee_id from hr.employees;
insert into emp (empno) select employee_id from hr.employees
*
ERROR at line 1:
ORA-01502: index 'DONGHUA.EMP_PK' or partition of such index is in unusable
state


SQL> alter table emp disable constraint emp_pk keep index;

Table altered.

SQL> insert into emp (empno) select employee_id from hr.employees;

107 rows created.

SQL>