Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: ORA-00001: unique constraint violated

Re: ORA-00001: unique constraint violated

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Wed, 17 Nov 2004 21:10:24 +1100
Message-ID: <419b238f$0$12900$afc38c87@news.optusnet.com.au>


Michael wrote:
> Hello Howard,
>
> wow ... you impress me ... and I agree in most things with you ... but
> look at the create table statement of for example a 9i database and you
> will see that you can define primary key constraints directly when
> creating the table .... this will automatically create a primary unique
> index of course ....

Not true. Unless you just want to create a table mindlessly, and ignoring all the syntactical subtleties that are at a sensible DBA's disposal. It all just depends on how clever you want to be with your create table statement. Let's just do things the 8.0 way for a moment, shall we?

SQL> connect scott/tiger
Connected.
SQL> drop table e;

Table dropped.

SQL> create table e(

   2 col1 number constraint epk primary key deferrable,    3 col2 char(10));

Table created.

And that's a deferrable constraint created at the time of creating the table. So let's see what sort of index got created:

SQL> select index_name, uniqueness from user_indexes;

INDEX_NAME                     UNIQUENES
------------------------------ ---------
EPK                            NONUNIQUE

So the statement "you will see that you can define primary key constraints directly when creating the table .... this will automatically create a primary unique index of course .... " must be said to be wrong too: there's no "of course" about it.

If you'd written "A basic 'create table' statement, that uses none of the syntactical capabilities available since 1998, and where the DBA makes no attempt to take control of the constraint creation process, will create unique indexes", I'd agree with you. But that's not quite the same thing as you actually wrote, is it?

Now, when it comes to 9i, it's even easier:

SQL> create table e(

   2 col1 number constraint epk primary key deferrable using index

                (create index IAMTHEINDEX on e(col1)
                 tablespace users pctfree 15),
   3 col2 char(10));

Table created.

In 9i, you can actually put a complete 'create index' statement inside a create table statement. There is no excuse whatsoever for not having complete control over your index and constraint creation process.

> most people forget to give this thing a name and
> therefor the constraint is named something like sys...., because it is
> a automatically generated system name ....

Well, I question the "most people" claim, since I spend a large part of my ptofessional life telling people "remember to name your constraints" and I'd hate to think that I was just wasting my breath. But not naming your constraints is one thing, and I could live with it even so. Having them deferrable or not deferrable (and hence enforced with a unique or non-unique index) is quite another. So, for example:

SQL> create table e(

   2 col1 number primary key deferrable,    3 col2 char(10));

Table created.

SQL> select index_name, uniqueness from user_indexes;

INDEX_NAME                     UNIQUENES
------------------------------ ---------
SYS_C002579                    NONUNIQUE

Here, a system generated name, but still a non-unique index helping to enforce uniqueness. It's remembering to say "deferrable" that's important -and for which there is no excuse if you forget it.

> As for my knowledge the create index command is still there and works
> of course, but the techique to build it up is a constraint. But I was
> only talking about the standard behavior ... not the exceptions or the
> later add ons.

My examples will work (unless otherwise explicitly stated) in every Oracle version since 8.0 without alteration. You may be an Oracle 5 or 6 user, but for most of us, these are not "later add ons". It's just the way Oracle works.

I really don't understand what you mean by "the technique to build it up is a constraint". Indexes and constraints have absolutely nothing to do with each other. It merely happens to be the case that if you demand I check that everything entering a table is unique, I'm going to find an index of some sort very helpful in resolving that question very quickly.

> The normal way it works is that the index then will be dropped or
> recreated

Why do you say that's normal? I teach all my students: every primary and unique key constraint you declare should be 'deferrable initially immediate'. Beacause if there's one thing you don't want happening as a DBA, it's a 60GB index deciding to do a disappearing act on you, without any warning whatsoever, simply because you disabled a constraint intending to do a 30-second maintenance job. With non-unique indexes, which the deferrable keyword gives you, you don't have indexes deciding to silently self-destruct. And you therefore don't have the massive table locking and performance problems associated with re-building an index that occurs when you innocently choose to re-enable the index.

It's having an index dropped in the first place that is abnormal. Most good DBAs will bend over backwards to stop that sort of nonsense happening.

I believe there is an entire cohort of Oracle DBAs in Australia who will doing nothing but creating deferrable inititally immediate constraints. I don't think they're likely to be particularly odd, either.

>... about the sense to keep an index when you manipulate a

> table with disabled constraints ... hm ... i fear this is another
> discussion.

It comes down to this: if the index is there merely to help the constraint be enforced quickly and without too much fuss, then disabling the constraint clearly implies that the index can be disposed of (though I'd still worry about the table locking and I/O involved when it comes time to re-enable the constraint). But if the index is there to help the constraint AND is useful for assisting in speeding up ordinary queries (likely to be true, I'd say, for any index associated with a primary key constraint), then disabling the constraint should absolutely, definitely NOT *automatically and inevitably* mean the index should be disposed of.

> Greetings,
> Michael
>
> ps: I'm really not perfect and I might be wrong, but I'm able to learn
> day for day .... :-)

That's fine. We're all in the same boat. I certainly don't claim to be perfect, and my enthusiasm for huge undo tablespaces in 9i Release 1 without thinking throught their performance-crippling consequences will always be something that I am embarrassed about. But in that case, it pays not to make statements about 'this is the way things work' or 'this is normal', when actually it's not the way things work, and it's definitely not normal.

Regards
HJR Received on Wed Nov 17 2004 - 04:10:24 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US