Re: Unique Constraint(SO_LINES_N10) Violated but Index is NOT Unique!

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 31 Dec 1999 16:15:59 -0500
Message-ID: <qt6q6sokfvk37pjqd2cnqbflbhulr8k3tp@4ax.com>

A copy of this was sent to philip_at_aleytys.pc.my (Philip Chee) (if that email address didn't require changing) On Fri, 31 Dec 99 20:33:54 GMT, you wrote:

>Well this is one for the gurus' here.
>
>I am getting an ORA error "unique constraint (SO_LINES_N10) violated".
>However poking at the index with TOAD shows that this index is indeed not
>unique. The TRM for that module also lists that index as non-unique.
>
>1. An Alter Index Rebuild doesn't work.
>2. Dropping and recreating the index DOES make the problem go away but
> the error comes back after a couple of days (or less if unlucky).
>

what error? you have a unique constraint and are violating it apparently?

>I have sent the trace files to Oracle customer support but so far they've
>come up blank - seems that the trace files just confirm that a unique
>constraint is violated.
>
>Jonathan? Anybody? Help!
>
>Philip (just spent four hours 10pm to 2am on Y2K watch at the office)
>

UNIQUE constraints do not *need* a unique index. There are at least 2 ways this can happen.

1. you created the table with a deferrable UNIQUE constraint. Consider:

tkyte_at_8.0> create table t ( x int unique deferrable );

Table created.

tkyte_at_8.0> @desc t
Datatypes for Table t

                               Data                           Data
Column Name                    Type                           Length

Nullable

------------------------------ ------------------------------ -----------
--------
X                              NUMBER                                     null

Indexes on t

Index                          Is
Name                           Unique Indexed Columns
------------------------------ ------ -----------------------------------
SYS_C0012043                   No     X

T has a non-unique index on it BUT it does have a unique constraint:

tkyte_at_8.0> insert into t values ( 1 );
tkyte_at_8.0> insert into t values ( 1 );
insert into t values ( 1 )

            *
ERROR at line 1:
ORA-00001: unique constraint (TKYTE.SYS_C0012043) violated

2) You created T, indexed X and then added a unique constraint. Consider:

tkyte_at_8.0> create table t   ( x int );
tkyte_at_8.0> create index x_idx on t (x);
tkyte_at_8.0> alter table t add constraint x_is_unique unique(x);
tkyte_at_8.0> @desc t

Datatypes for Table t

                               Data                           Data
Column Name                    Type                           Length

Nullable

------------------------------ ------------------------------ -----------
--------
X                              NUMBER                                     null

Indexes on t

Index                          Is
Name                           Unique Indexed Columns
------------------------------ ------ -----------------------------------
X_IDX                          No     X

tkyte_at_8.0>

We only need an index -- not a unique index to support a unique constraint (or primary key even) in 8.0 and up...

To see if a unique constraint exists, just "select * from user_constraints where table_name = 'TABLE_IN_QUESTION' and constraint_type = 'U';

>---=====================================================================---
> Philip Chee: Tasek Corporation Berhad, P.O.Box 254, 30908 Ipoh, MALAYSIA
> e-mail: philip_at_aleytys.pc.my Voice:+60-5-545-1011 Fax:+60-5-547-3932
> Guard us from the she-wolf and the wolf, and guard us from the thief,
> oh Night, and so be good for us to pass.
>---
> þ 20156.73 þ Proceed with Caution - Twisted Mind Under Construction!

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Dec 31 1999 - 15:15:59 CST