Re: Disabling Existing Constraints and Index dropped

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>
Received on Tue Aug 06 2002 - 14:45:13 CDT