Re: unique contraint without index

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 16 Jan 2007 15:09:04 -0800
Message-ID: <1168988938.754773@bubbleator.drizzle.com>

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

Received on Tue Jan 16 2007 - 17:09:04 CST