Re: Disabling Existing Constraints and Index dropped

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

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>