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