Re: unique contraint without index

"Jaap W. van Dijk" <j.w.vandijk.removethis_at_hetnet.nl> schreef in bericht news:45adf219.2564359_at_news.hetnet.nl...
> On Tue, 16 Jan 2007 15:09:04 -0800, DA Morgan <damorgan_at_psoug.org>
> wrote:
>
>>Jaap W. van Dijk wrote:
>>> On 16 Jan 2007 06:06:39 -0800, "sybrandb" <sybrandb_at_gmail.com> wrote:
>>>
>>>
>>>> A primary key without any index just doesn't make sense.
>>>> Nor does your nick.
>>>>
>>> Yes it does, and Oracle thinks so too (DISABLE NOVALIDATE).
>>>
>>> Jaap.
>>
>>That is a special case only valid when using a non-deferrable
>>constraint and/or when the constraint is built using a unique
>>index (rather than a non-unique) index.
>>
>>SQL> ALTER TABLE t
>> 2 ADD CONSTRAINT pk_t
>> 3 PRIMARY KEY (testcol)
>> 4 DEFERRABLE INITIALLY DEFERRED
>> 5 USING INDEX;
>>
>>SQL> ALTER TABLE t
>> 2 MODIFY CONSTRAINT pk_t
>> 3 DISABLE NOVALIDATE;
>>
>>Table altered.
>>
>>SQL> select constraint_name from user_constraints where table_name = 'T';
>>
>>CONSTRAINT_NAME
>>------------------------------
>>PK_T
>>
>>SQL> select index_name from user_indexes where table_name = 'T';
>>
>>INDEX_NAME
>>------------------------------
>>PK_T
>>
>>SQL>
>>--
>>Daniel A. Morgan
>>University of Washington
>>damorgan_at_x.washington.edu
>>(replace x with u to respond)
>>Puget Sound Oracle Users Group
>>www.psoug.org
>
> You are right, but Sybrand made an unqualified remark:
>
> "A primary key without any index just doesn't make sense"
>
> and there are situations when this can make sense (from the SQL
> Reference):
>
> "This feature [DISABLE VALIDATE, Jaap] is most useful in data
> warehousing situations, because it lets you load large amounts of data
> while also saving space by not having an index."
>
> To which I can add: and saving time by not having to write the index
> segment. You can not change the data now of course, but this is
> usually acceptable in a datawarehouse, where you only want to query
> the data for reporting after you have loaded it.
>
> Jaap.

In which case, to my opinion, the constraint itself is of no value at all. If you don't update, why set a constraint?

Shakespeare Received on Wed Jan 17 2007 - 09:30:33 CST