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';

INDEX_OWNER	INDEX_NAME
SCOTT	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';

OBJECT_NAME	OBJECT_TYPE
TEST_ID	INDEX