Re: Preferred method in creating primary key

"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)