Re: Preferred method in creating primary key

It is so very very good to have Quarkman, HJR, or whatever Queen Elizabeth has knighted him as, back in our realm

Daniel Morgan wrote:

> "Howard J. Rogers" wrote:
>
> > > What a great way to corrupt a table.
> >
> > Hang on, hang on. This isn´t a bug. Nothing like it. And it has been
> > discussed here many, many times: its actually a very nice feature.
> >
> > If a primary key is enforced with a unique index, then it makes no sense
> > to ever disable the constraint -because the constraint might be switched
> > off, but an index would still be in place taking it upon itself to enforce
> > uniqueness. Hence, disabling the constraint would be utterly ineffective
> > -unless you were simultaneously to drop the index. Which is exactly what
> > Oracle does. Without any warning or asking for confirmation, a quick
> > ¨alter table blah disable constraint¨ causes potentially very large
> > indexes to disappear, meaning they have to be rebuilt when you re-enable
> > the constraint... and the table is locked for the duration of what could
> > be a very long rebuild.
> >
> > Likewise, what use would ¨deferred constraints¨ actually be if there was
> > a unique index in place? The table would be saying ¨duplicates are
> > temporarily permitted to reside in the table, until you commit, because
> > you want this thing deferred¨. But the index would be sitting there
> > saying ¨violating record! Chuck it out!!¨.
> >
> > So again, if a constraint might ever be deferred, it has got to have a
> > non-unique index to back it up, or no index at all. Oracle´s use of
> > non-unique indexes to enforce primary keys and unique constraints is
> > therefore not a bug, but a perfectly sound bit of logical reasoning. And
> > it has management advantages, too -I speak as one who inadvertently had 90
> > people sent home for the day (this was way back in 1997, in my defence)
> > because I chosen to do a two-minute disable-reenable of a constraint, not
> > realising that a many-multi-gigabyte index was going to need to be rebuilt
> > as a result. These days, all my primary and unique constraints are
> > declared deferrable, precisely because I get non-unique indexes to enforce
> > them. And they only disappear when I ask them to.
> >
> > Regards
> > HJR
>
> I appreciate all this (BTW ... was that a strange or a charmed quark?) but I
> have never understood the concept of disabling a primary key constraint unless
> the point is to destroy the relational nature of the schema.
>
> The only reason to disable a primary key is to insert records that violate it.
> Given that the key can then, never be re-enabled, why not just drop it and
> eliminate the issue entirely?
>
> --
> Daniel Morgan
> http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
> damorgan_at_x.washington.edu
> (replace 'x' with a 'u' to reply)
Received on Wed Aug 13 2003 - 21:14:28 CDT