Re: Disabling Existing Constraints and Index dropped

"Howard J. Rogers" wrote:
>
> "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?
>

Hi Howard,

OK, this is one possible scenario (not saying it's what I would necessarily do).

By disabling the unique constraint, you automatically disable (cascade) all referencing foreign key constraints. This means you can perform a load on all such child table(s) and temporarily have data that violates these FK constraints. Currently it's no drama as these FK constraints are disabled (and you haven't had to manually disable each and every one).

During this process the unique constraint was being effectively policed while it was disabled (as the unique index was not dropped) and the index has not had to be rebuilt when the constraint is enabled again (as it was never dropped).

Yes you could have achieved the same by disabling all the corresponding FKs but this could now be a handy quick method.

I tried :)

Richard

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