Creando índices únicos y restricciones

Si creas una tabla con una cláusula primary key en la sentencia de create table, Oracle creará la tabla y un índice único para mantener la unicidad de la llave primaria, y si se borra la restricción se borrará también el índice único que le acompaña:

SQL> create table miprueba (id number primary key, fecha date);
Table created.

SQL> insert into miprueba values (1, sysdate);

1 row created.

SQL> insert into miprueba values (1, sysdate);
insert into miprueba values (1, sysdate)
*
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.SYS_C004346) violated

SQL> select table_NAME from dba_indexes where index_name='SYS_C004346';

TABLE_NAME
------------------------------
MIPRUEBA

SQL> alter table miprueba drop constraint SYS_C004346;

Table altered.

SQL> select table_NAME from dba_indexes where index_name='SYS_C004346';

no rows selected

SQL> select * from dba_constraints where CONSTRAINT_NAME='SYS_C004346';

no rows selected

SQL> drop table miprueba;

Table dropped.

Y si creas primero la tabla, después el índice único y al final agregas la restricción, el índice y la restricción pueden tener el mismo nombre, pero incluso si la restricción usa el índice único creado para mantener la restricción de la llave primaria, si se elimina la restricción no se estará borrando el índice único debido a que fueron creados por separado:

SQL> create table miprueba(id number, fecha date);

Table created.

SQL> create unique index pk_miprueba on miprueba (id);

Index created.

SQL> select INDEX_NAME from dba_indexes where TABLE_NAME='MIPRUEBA';

INDEX_NAME
------------------------------
PK_MIPRUEBA

SQL> ALTER TABLE miprueba add (constraint pk_miprueba primary key (id));

Table altered.

SQL> select constraint_name from dba_constraints where table_NAME='MIPRUEBA';
CONSTRAINT_NAME
------------------------------
PK_MIPRUEBA

SQL> select INDEX_NAME from dba_indexes where TABLE_NAME='MIPRUEBA';

INDEX_NAME
------------------------------
PK_MIPRUEBA

SQL> alter table miprueba drop constraint pk_miprueba;

Table altered.

SQL> select INDEX_NAME from dba_indexes where TABLE_NAME='MIPRUEBA';

INDEX_NAME
------------------------------
PK_MIPRUEBA

SQL> select constraint_name from dba_constraints where table_NAME='MIPRUEBA';

no rows selected