Re: UNIQUE CONSTRAINT ERROR

Comments embedded.
Paul wrote:
> I have some PL/SQL code that has been running for several years and just started giving me problems. It inserts a record from one database to another going across a dblink.
>
> It recently started giving me this error:
>
> ORA-00001: unique constraint (SIEBEL.S_EVT_ACT_M9) violated

A perfectly valid error.

>
> The problem is that SIEBEL.S_EVT_ACT_M9 is an index, but it is NOT a unique index.
>

It need not be:

Create a table of unique values --

SQL> create table uniqtest
  2 as
  3 select distinct object_name
  4 from user_objects;

Table created.

Create a non-unique index on these unique values --

SQL> create index myindx
  2 on uniqtest(object_name);

Index created.

Create a unique constraint using that non-unique index --

SQL> alter table uniqtest
  2 add constraint my_uniq_const
  3 unique (object_name)
  4 using index myindx;

Table altered.

Verify only unique values can enter the table --

SQL> insert into uniqtest
  2 select object_name
  3 from user_objects;
insert into uniqtest
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.MY_UNIQ_CONST) violated

In your case the constraint would be created DEFERRED, to prevent checking existing data for violations; subsequent inserts would then be subject to compliance with the constraint with those violating it producing the error you report.

> When I had the problem a week ago I suspected a corrupt index, so I dropped and recreated the index. This fixed the problem. However the problem came back this week.
>

Likely dropping the index also dropped whatever unique constraint depended upon it. It is also likely that someone found the constraint missing and replaced it. Look in USER_CONSTRAINTS for an entry or entries with CONSTRAINT_TYPE = 'U' associated with this table and determine, if multiple records exist, which constraint is the culprit by examining USER_CONS_COLUMNS, matching the columns to those in this non-unique index.

> So now I need to get to the root cause and find out what is happening to this index.
>

Probably nothing; it may be someone adding a unique constraint utilising that non-unique index.

> Any help would be appreciated.
>
> Thank you.
> --
> Paul LaBarbera
> Oracle DBA Manager, AT&T, Morristown, NJ

David Fitzjarrell Received on Thu May 25 2006 - 14:22:35 CDT