Re: Preferred method in creating primary key

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Thu, 14 Aug 2003 06:11:42 +1000
Message-ID: <pan.2003.08.13.20.10.18.373499@yahoo.com.au>

On Wed, 13 Aug 2003 12:51:59 -0700, Daniel Morgan wrote:

> Jack Wang wrote:
> 

>> A primary key is NOT necessary to have a unique index. Consider,
>>
>> CREATE TABLE T1...
>>
>> SQL> ALTER TABLE T1
>> ADD CONSTRAINT T1_PK
>> PRIMARY KEY (Y)
>> DEFERRABLE
>> INITIALLY IMMEDIATE USING INDEX
>> TABLESPACE INDX;
>>
>> SQL> SELECT INDEX_NAME, UNIQUENESS FROM USER_INDEXES WHERE INDEX_NAME =
>> 'T1_PK';
>>
>> UNIQUENES INDEX_NAME
>> --------- ------------------------------ NONUNIQUE T1_PK
>>
>> DROP TABLE...
>> CREATE TABLE...
>>
>> SQL> ALTER TABLE T1
>> ADD CONSTRAINT T1_PK
>> PRIMARY KEY (Y)
>> USING INDEX
>> TABLESPACE INDX;
>>
>> SQL> SELECT INDEX_NAME, UNIQUENESS FROM USER_INDEXES WHERE INDEX_NAME =
>> 'T1_PK';
>>
>> UNIQUENES INDEX_NAME
>> --------- ------------------------------ UNIQUE
>> T1_PK
>>
>> So the conclusion is if the constraint is enabled and there is no index
>> that uses the constraint columns as a leading part of the index, an
>> index with the same name as the constraint is created using the
>> following rules: - If the key is deferrable, a nonunique index on the
>> key column is created. - If the key is nondeferrable, a unique index is
>> created.
>>
>> Jack
>>
>>
>> > No difference exists, subtle or otherwise. A primary key MUST have a
>> unique
>> > index.
>> >
>> > My preference is to NEVER create an index. In fact I can't think of
>> > any
>> good
>> > reason to ever do so.
>> >
>> > CREATE TABLE ...
>> >
>> > ALTER TABLE <table_name>
>> > ADD CONSTRAINT pk_ ...
>> > PRIMARY KEY (<column_name_list>)
>> > USING INDEX
>> > TABLESPACE ...
>> >
>> > of course, as Norman points out, ... with DEFERRABLE and INITIALLY
>> IMMEDIATE.
>> >
>> > --
>> > 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)
>> >
>> >
>> >

> Frightening. If this isn't a bug ... who-ever designed it should be
> shown the door.
> 
> 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 Received on Wed Aug 13 2003 - 15:11:42 CDT