Creating unique indexes and constraints

If you create a table with a primary key clause in the create table statement, Oracle will create the table and also an unique index to enforce the uniqueness of the primary key, and if you drop the constraint you will drop the unique index too:

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.

But if you create a table first and later create an unique index and finally add a constraint, you can name the index and the constraint the same, and even if the constraint uses the existing unique index to enforce the primary key constraint, if you drop the constraint you won't be dropping the unique index because they were created separately:

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