Re: Disabling Existing Constraints and Index dropped

"Richard Foote" <Richard.Foote_at_oracle.com> wrote in message news:3D4F17B2.1DF1F778_at_oracle.com...
> 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).

Depends what you mean by 'overide'. It is obviously a logical non-starter to keep a unique index on a primary key/unique constraint which you want to disable -no matter that the table might want to permit the insertion of duplicating records, the index will still be there throwing out the potential trouble-makers. If you truly, really want to disable such a constraint, you are forced by dint of simple logic to drop all unique indexes used to support said constraint.

The fact that you syntactically can keep a unique index when disabling a constraint is a logical nonsense, and I'm rather surprised that 9i permits such glaring contradictions to be submitted under the guise of good SQL!

As a for instance:

SQL> create table daftone as select * from emp;

Table created.

SQL> alter table daftone add (constraint daftpk primary key(empno));

Table altered.

SQL> select index_name, uniqueness from dba_indexes where index_name like 'DAFT%';

INDEX_NAME                     UNIQUENES
------------------------------ ---------
DAFTPK                         UNIQUE

SQL> alter table daftone disable constraint daftpk keep index;

Table altered.

SQL> insert into emp (empno, ename) values (7839, 'KINGTWO'); insert into emp (empno, ename) values (7839, 'KINGTWO') *
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.PK_EMP) violated

So, the point of my disabling the constraint was.... er, what exactly?

Regards
HJR
> 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>
Received on Tue Aug 06 2002 - 04:18:19 CDT