Re: Needs help urgent..

Hi fellow Richard,

You're not (quite) correct in saying that indexes will be removed if you disable a PK constraint (since Oracle8 anyway).

It actually depends.

It depends on whether a non unique index is being used to police the constraint, because if it is then the index is *not* dropped.

A non unique index could be used under at least the following situations:

1. The constraint has been defined as deferrable
2. A non unique index was created prior to the constraint being applied and Oracle has simply reused it rather than creating a new one.

In 9i, there is also the option to overwrite this behaviour with the DROP index clause.

Just clarifying for all the lurkers out there.

Cheers

Richard
"Richard Spee" <rhpspee_at_wxs.nl> wrote in message news:am7qsq$46v$1_at_reader12.wxs.nl...
> Beware.
>
> If you disable a primary key constraint then the associated index WILL BE
REMOVED.
> (unless you are working with Oracle9 and used the KEEP INDEX clause)
>
> "Brian E Dick" <bdick_at_cox.net> wrote in message
news:viFh9.65493$8F4.1594365_at_news2.east.cox.net...
> > You are violating a basic rule of relational databases. Primary keys are
> > immutable.
> >
> > Now that you have been sufficiently scolded, I see two choices (you
> > eliminated a third).
> >
> > 1) As another posted recommended, insert a new master, update the
detail,
> > delete the old master.
> > 2) Drop the constraint and recreate it as deferrable. For more info,
check
> > the docs.
> >
> > "priya" <priya_at_starcomsoftware.com> wrote in message
> > news:1ae23258.0209162213.66a85c16_at_posting.google.com...
> > > Hi..
> > >
> > > I want to update two tables which have ref. integrity constraints
among
> > them.
> > > When I'm updating master table's primary key it gives me error as
> > >
> > > 'Integrity constraint violated .. child record found'
> > >
> > > When I'm updating detail table's ref. key gives me error as
> > >
> > > 'Integrity constraint violated.. no parent record found'
> > >
> > > Is there any way to solve above problem other than enabling /
disabling
> > > constraints before updattion ?
> > >
> > > Thanks,
> > > Priya
> >
> >
>
>
Received on Tue Sep 17 2002 - 19:31:24 CDT