Re: Disabling Existing Constraints and Index dropped

Hi Howard,

Oh, it's something I do all the time and love doing too. NOT.

I agree it's pretty daft, but hey, at least it's another question we can add to OCP.

Cheers

Richard

"Howard J. Rogers" wrote:
>
> It's a bit of a stretch as examples go!! But it's not a bad one.
>
> Next time I want to disable a bunch of foreign keys because I want to insert
> violating records into the child table, yet not introduce duplicating
> records into the parent table, I'll be sure to remember it!! I'm sure I do
> this every day ;-)
>
> (Incidentally, I think the idea of being able to explicitly drop a
> *non-unique* index as you disable a constraint is brilliant, and just what
> the doctor ordered. Your example aside, I still think the other-way-round is
> logically daft, though).
>
> Regards
> HJR
>
> "Richard Foote" <Richard.Foote_at_oracle.com> wrote in message
> news:3D4FC5B7.ECBA9B96_at_oracle.com...
> >
> >
> > "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>