Re: ORA-00001: unique constraint violated

Michael wrote:
> Hello Howard,
>
> just at first ... I do not want to run into philosophical analyses ...
> but I think that we both do not come together because we look at the
> things with different view.
>
> Viewed from the side of the relational theory a index and a constraint
> does nothing have in common.

Viewed from any side you like. They are two different objects inside an Oracle database, too. The index is there merely to help the constraint do its job efficiently.

> And nearly all database have their own way
> to build them up. Sure you can create a primary and also a unique index
> without to add a constraint to the table ... just that I (because of
> working with Oracle) prefer to add a primary or unique constraint to a
> table which will automatically create the necessary indexes for me ..
> it's standard behavior of Oracle as far I know ... instead of writing a
> separate create index script. Maybe a problem of my prefered way to do
> it ...

There is no difference between us on that. I have shown you SQL statement after SQL statement where I create *constraints* and where Oracle will accordingly create my indexes for me automatically. That is how I do it, that is how it should be done.

My point to you throughout has been that yes, "an" index will be "automatically created" when you specify table primary key or unique constraints, but that *you* have complete control over the *type* of index that gets created. Of course there are defaults, but it's the job of the good DBA to know about these things and control them ("administer" them), not just give in and accept those defaults.

Surely you don't accept just create your tables with only ever the 10% default PCTFREE? Surely you think about whether that default is appropriate for the table you are about to create? You then modify it as needed. You take control of the process.

Well, the same goes for constraints. You either just accept all of Oracle's defaults and live with the unique indexes that'll get created automatically... or you become a good DBA and take charge of that process, deciding *for yourself* what sort of indexes you'll allow to exist in your own database.

My own view is that the word "deferrable" ought to make an appearance in nearly every primary/unique constraint definition. But whether you agree with that or not, the point is that you should know about it and make an intelligent decision about it. And, perhaps more to the point in this forum, that you shouldn't claim things to be the case regarding the process which aren't.

There is nothing philosophical about that. Either one knows, technically, what happens when you declare constraints in your create table statements. Or you don't. The matter is capable of clarification, which I've tried to do with you. But that is not the same thing as having a discussion -because for there to be a discussion, we both have to be right. And sadly, on this occasion, you aren't (about the specific things I've already quoted in the earlier posts, that is).

> To be honest i have the feeling that you seems to be the better one to
> explain this background and I have no problems to commit this.

That's fine. Let's leave it there then.

To make statements about anything, it is necessary to know the technical facts and to be able to re-state those facts clearly, precisely and unambiguously. And, of course, there are degrees of all those things.

HJR
> Greetings,
> Michael
>
Received on Wed Nov 17 2004 - 13:48:08 CST