Re: Disabling Existing Constraints and Index dropped

From: Ender Wiggin <ender_wiggin29plus1_at_yahoo.com>
Date: Mon, 05 Aug 2002 21:59:28 -0600
Message-ID: <NCH39.381$Q9.183022@newsfeed.slurp.net>

Read below for my single comment

Richard Foote wrote:

> Hi Phil,
> 
> Yes, Oracle will create the index automatically again for you. A couple
> of points though.
> 
> Firstly, it's not good practice to just create or enable a Primary Key
> or Unique key and let Oracle just create the index on its own. I would
> strongly recommend the use of the USING INDEX clause to control how the
> index is created (such as it's name and tablespace). Note 9i allows you
> to define the entire create index syntax when creating/enabling these
> constraints.
> 
> Also note Oracle doesn't necessarily create or drop the indexes
> associated with the constraint. If the index being used by Oracle is non
> unique (possible for deferrable constraints) then the index is not
> dropped automatically (although 9i can override this default behavior).
> If an existing index could be used to police a new constraint (in that
> the leading columns of an existing index match the column constraints),
> then Oracle will use the existing index and will not create an
> unnecessary index.
> 
> So there's a fair bit to all this :)

Well then u loose uniqueness in your index and you incur read costs for unique searches. Not much but why should you ?

> 
> Cheers
> 
> Richard
> 
> 
> phil wrote:

>>
>> Hi,
>>
>> If I disable an existing constraints (primary key), it seems the index
>> would be dropped too.
>> How about I enable the primary key constraints again, will it create the
>> index or I have to create the index by myself?
>>
>> Thanks for your help.
>>
>> Phil
>>
>> Examples:
>> SQL> DROP INDEX SCOTT.PK_EMP;
>> DROP INDEX SCOTT.PK_EMP
>> *
>> ERROR at line 1:
>> ORA-02429: cannot drop index used for enforcement of unique/primary key
>>
>> SQL> ALTER TABLE SCOTT.EMP DISABLE PRIMARY KEY;
>>
>> Table altered.
>>
>> SQL> DROP INDEX SCOTT.PK_EMP;
>> DROP INDEX SCOTT.PK_EMP
>> *
>> ERROR at line 1:
>> ORA-01418: specified index does not exist
>>
>> SQL> ALTER TABLE SCOTT.EMP ENABLE PRIMARY KEY;
>>
>> Table altered.
>>
>> SQL>
Received on Mon Aug 05 2002 - 22:59:28 CDT