Unique constraint violation even after Constraint is dropped

Problem:

I am getting error ORA-00001: unique constraint violation error while I can’t find the constraint.

Solution:

You need to check the index, there might be an index which giving you the ORA-00001: unique constraint violation error.

Example:

SQL> create table test (id number not null, name varchar2(100) not null);

Table created.

SQL> create unique index test_id on test (id);

Index created.

SQL> alter table test add constraint test_pk primary key (id) using index test_id;

Table altered.

SQL> insert into test values (1, 'Joachim');

1 row created.

SQL> insert into test values (1, 'Lars');

insert into test values (1, 'Lars')

*

ERROR at line 1:

ORA-00001: unique constraint (SCOTT.TEST_PK) violated

SQL> alter table test drop constraint test_pk;

Table altered.

SQL> insert into test values (1, 'Lars');

insert into test values (1, 'Lars')

*

ERROR at line 1:

ORA-00001: unique constraint (SCOTT.TEST_ID) violated

SQL> SELECT owner, table_name

FROM user_constraints

WHERE constraint_name = 'TEST_ID';

no rows selected

Now let’s check the index.

select index_owner,index_name from all_ind_columns where index_name ='TEST_ID';

You can also check object by using the below query as SYS user

SQL> SELECT object_name,object_type

FROM dba_objects

WHERE owner = 'SCOTT'

AND object_name = 'TEST_ID';