Skip to Main Content
  • Questions
  • unique constraint violation on a disabled constraint

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question.

Asked: April 15, 2010 - 9:22 pm UTC

Last updated: April 19, 2010 - 8:17 am UTC

Version: 10.2.0.4

Viewed 10K+ times! This question is

You Asked

I've been instructed to take some ddl from one database, and data from another, for just one user, but it isn't a perfect match and on the import of the data I was getting slews and slews of

IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (DAS.PKAST) violated

so I killed the import, rebuilt, and disabled all the constraints with
select 'alter table '||a.table_name||' disable constraint '||
a.constraint_name||'cascade ;' from user_constraints a
/

and received no errors when disabling them.
I know there is a problem ahead, but want to get the data in and then report back
what constraints are not going to work.

But this confuses me. The constraints are disabled. Then.. I got the exact same error.. on a disabled constraint.

So I go straight to the table and try inserting a duplicate row, and sure enough, a unique constraint violation, but the constraint is disabled.

Can you explain this output?

SQL> desc ast_pty;
Name Null? Type
----------------------------------------- -------- ----------------------------
APPL_ID NUMBER(38)
USER_ID NUMBER(38)
ROLE_ID NUMBER(38)
UPDATED_BY VARCHAR2(30)
UPDATE_TIMESTAMP DATE

SQL> insert into ast_pty values (1,1,1,'a',sysdate);

1 row created.

SQL> /
insert into ast_pty values (1,1,1,'a',sysdate)
*
ERROR at line 1:
ORA-00001: unique constraint (DAS.PKAST) violated


SQL> select status from user_constraints
2 where constraint_name = 'PKAST';

STATUS
--------
DISABLED

SQL>

??

and Tom said...

sounds like a unique index. It'll raise the same ora-00001.


ops$tkyte%ORA10GR2> create table t ( x int );

Table created.

ops$tkyte%ORA10GR2> create UNIQUE index t_unique on t(x);

Index created.

ops$tkyte%ORA10GR2> alter table t add constraint t_unique unique(x);

Table altered.

ops$tkyte%ORA10GR2> alter table t disable constraint t_unique;

Table altered.

ops$tkyte%ORA10GR2> insert into t values ( 1 );

1 row created.

ops$tkyte%ORA10GR2> insert into t values ( 1 );
insert into t values ( 1 )
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$TKYTE.T_UNIQUE) violated


ops$tkyte%ORA10GR2> drop index t_unique;

Index dropped.

ops$tkyte%ORA10GR2> insert into t values ( 1 );

1 row creat

Rating

  (12 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

I concur with Tom

John Hawksworth, April 16, 2010 - 2:45 pm UTC

I've found precisely this circumstance myself. It arises when someone tries to be clever, or "help" oracle with enforcing uniqueness.

Folks realise that Oracle enforces unique constraints by means of an automatically generated unique index "behind the scenes". Such "clever" folk then short-circuit the process and create the unique index directly, which appears to do what they want. Some poor DBA comes along later, disables the unique constraint, and then gets the symptoms you describe, as the index continues to do its thing.

As far as I am concerned, a manually created unique index is a bug, unless there is a business reason to make one, and nobody (in my 15 years of Oracle work) has ever come up with one. That's why my DB standards document expressly forbids unique indexes, but commends unique constraints.

Chuck Jolley, April 16, 2010 - 3:09 pm UTC

You never use surrogate keys?

More on unique bits

John Hawksworth, April 16, 2010 - 3:59 pm UTC

Er, well, yes, I nearly always use surrogacy, and use sequence generated integers for PK's. Your question leads me to believe that you're questioning how I avoid manually creating a unique index, to support said surrogate - is that correct?

If it is, then please note I specifically mentioned MANUAL index creation, and contrasted this with that done automatically by Oracle. When I declare the PK constraint on a surrogate, Oracle does indeed create an index "behind the scenes" but the only DDL I issue will be to create the PK, I will deliberately NOT issue DDL to create the requisite unique index.

I repeat, I consider a MANUALLY created unique index to be a bug, unless there is a business reason to do so. Please state your business reason for making one, because I'm curious as to what it might be.

Chuck Jolley, April 16, 2010 - 4:12 pm UTC

Because if I'm going to be looking at the table data with the expectation of using that index then I want to explicitly create the index.
For the same reason I would never depend on a union to sort my result set.


Uniqueness (will wash ash off car tomorrow)

John Hawksworth, April 16, 2010 - 4:29 pm UTC

Chuck,

I think (but obviously can't know) that you've shot yourself in the foot there me ol' fruit.

You said that you would be looking at the table data in the expectation of using an index, well that's absolutely fine. Since your point was about surrogacy, and a surrogate key is a meaningless number, you obviously won't be expecting to use an index on that - right? Inspecting a number which is by definition meaningless is an exercise in futility.

If you have a unique constraint on the meaningful data in said table (enforced by an AUTOMATICALLY created unique index) and you expect the optimiser to use it, well and good - way to go.

Apart from uniquely identifying a row, what is the connection between table data and a meaningless surrogate key?

If you're expecting uniqueness across multiple columns in said table, then clearly there's a business reason to do so, but you declared a compound unique constraint didn't you? Surely not a unique index - unless of course it was a function based index to do something like conditional uniqueness, but then of course, that would be justified by business requirements ......

Doesn't look like there's an index though.

A reader, April 16, 2010 - 4:33 pm UTC

I thought about that index thing, but if I query user_ind_columns, it looks like the only index is the one implicitly generated by the primary key. 

SQL> select * from user_ind_columns where table_name
  2  = 'AST_USR_ROL';

INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
------------------------------------------------------------------------------------------------------------------------
COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESC
--------------- ------------- ----------- ----
PKAST                          AST_PTY
USER_ID
              1            22           0 ASC

PKAST                          AST_PTY
APPL_ID
              2            22           0 ASC

PKAST                          AST_PTY
ROLE_ID
              3            22           0 ASC


Tom Kyte
April 16, 2010 - 5:09 pm UTC

didn't you just show here that there is in fact an index???


And?

John Hawksworth, April 16, 2010 - 4:42 pm UTC

Well, if the only index is the automatically generated PK index, is that a problem?

yes, I was hiding the table name unsuccesfully

A reader, April 16, 2010 - 4:46 pm UTC

I notice in my post I forgot to edit the table name in the select statement. Sorry about that. The query output is genuine, I assure you.

Struggling to undrestand

John Hawksworth, April 16, 2010 - 4:57 pm UTC

Reader from CT (Connecticut?)

I assume you've got the PK index expected, and I can't see a problem with that. What am I missing? Please bear in mind that my side 'o' the pond it's late, and after a day of SQL Server bashing (geck!) I'm wilting fast.

Is that index the implicit PK index or what?

A reader, April 16, 2010 - 9:03 pm UTC

I am having trouble reconciling what you said - didn't I just prove there was an index, and this output.  Oracle 10.2.0.4

Connected.
SQL> create table example (a numbeR);

Table created.

SQL> alter table example add constraint pkindexauto primary key (a);

Table altered.

SQL> alter table example disable constraint pkindexauto;

Table altered.

SQL> insert  into example values (1);

1 row created.

SQL> /

1 row created.

SQL> select * from user_ind_columns where table_name = 'EXAMPLE';

no rows selected

SQL> alter table example enable constraint pkindexauto;
alter table example enable constraint pkindexauto
*
ERROR at line 1:
ORA-02437: cannot validate (SECURITY.PKINDEXAUTO) - primary key violated

SQL> delete from example;

2 rows deleted.

SQL> commit;

Commit complete.

SQL> alter table example enable constraint pkindexauto;

Table altered.

SQL>  select * from user_ind_columns where table_name = 'EXAMPLE';

INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESC
--------------- ------------- ----------- ----
PKINDEXAUTO                    EXAMPLE
A
              1            22           0 ASC


SQL>



Now, this looks like when I disable the primary key constraint, the index OF THE SAME NAME as the constraint, 
disappears, and then comes back with the same name when
I put the constraint back.  But in my above example,
when the constraint is disabled, the index of the same
name is still there.  How does that happen? Did someone
actually create an "extra" index with the same name
as the constraint? 

Tom Kyte
April 17, 2010 - 12:49 pm UTC

my index WAS OF THE SAME NAME as the constraint? so what - if you manually create the index AND THEN add the constraint, current releases recognize the two are separate and a disable doesn't drop that which you explicitly created.


ops$tkyte%ORA10GR2> create table t ( x int, y int );

Table created.

ops$tkyte%ORA10GR2> create UNIQUE index i_created_this on t(x);

Index created.

ops$tkyte%ORA10GR2> alter table t add constraint i_created_this unique(x);

Table altered.

ops$tkyte%ORA10GR2> alter table t add constraint i_didnt_create_this unique(y);

Table altered.

ops$tkyte%ORA10GR2> select index_name, uniqueness from user_indexes where table_name = 'T';

INDEX_NAME                     UNIQUENES
------------------------------ ---------
I_DIDNT_CREATE_THIS            UNIQUE
I_CREATED_THIS                 UNIQUE

ops$tkyte%ORA10GR2> alter table t disable constraint i_created_this;

Table altered.

ops$tkyte%ORA10GR2> alter table t disable constraint i_didnt_create_this;

Table altered.

ops$tkyte%ORA10GR2> select index_name, uniqueness from user_indexes where table_name = 'T';

INDEX_NAME                     UNIQUENES
------------------------------ ---------
I_CREATED_THIS                 UNIQUE




You were asking how this could happen, my premise is

a) table was created
b) unique index was added
c) then constraint came into being

that would exhibit this behavior.

ok.. fair enough

A reader, April 17, 2010 - 2:14 pm UTC

Welp.. I guess that's what happened. So I'll extract the ddl for unique indexes and dump 'em. If they patch other primary key constraints that will create indexes anyway that are unique, it seems the primary key should do the job. Unless you think there is a compelling reason not to. I assume in the life of this database, the need for uniqueness predated the need for a foreign key or something to that effect.
Tom Kyte
April 19, 2010 - 8:17 am UTC

sometimes people create indexes using create index (instead of using index in the constraint) to get it "just so".

You could

alter table t add constraint t_pk
using index (create unique index t_pk .... )
primary key(x);


or you could

create unique index t_pk ....;
alter table t add constraint t_pk primary key(x);


so, make sure you don't have that case - where the create index was there to get it into a certain tablespace, with certain pctfree, with whatever.

MIsleading error

Andy, December 10, 2010 - 6:16 am UTC

"ORA-00001: unique constraint (OPS$TKYTE.T_UNIQUE) violated"

Isn't this misleading? It says "unique constraint", when in fact it is "unique index".

Would be helpful if Oracle reported the correct reason.