Re: alter table disable constraint

From: Howard J. Rogers <howardjr_at_www.com>
Date: Wed, 7 Feb 2001 12:55:30 +1100
Message-ID: <F02g6.135$305.48521@inet16.us.oracle.com>

Here's the syntax I was thinking of. Let me know if it does what you are after, or if I have to find something else:

select pk.owner, pk.constraint_name, pk.constraint_type,

          pk.table_name, fk.owner, fk.constraint_name,
          fk.constraint_type, fk.table_name

from dba_constraints pk, dba_constraints fk where pk.constraint_name = fk.r_constraint_name(+) and pk.constraint_type='P';

It lists all Primary Key constraints, and shows any which have foreign key relations.

The list could potentially be huge, so you might want this variant:

"Naushi Hussain" <naushi.hussain_at_alliedsignal.com> wrote in message news:3A8055C7.714D0E4E_at_alliedsignal.com...
> you help will be greatly appreciated
>
> "Howard J. Rogers" wrote:
>
> > "Naushi Hussain" <naushi.hussain_at_alliedsignal.com> wrote in message
> > news:3A802540.F4FDA1A1_at_alliedsignal.com...
> > > 1) I did an alter table disable primary key cascade and it worked.
 Now
> > > I like to enable it. Will enabling automatically recreate the FK
> > > constraint?
> > >
> >
> > No. There's no 'enable constraint cascade' syntax.
> >
> > > 2) Also I tried looking in dba_constraints table for the table this
 key
> > > is a FK to. But I could not find the related table_name even in the
> > > R_CONSTRAINT_NAME. How can I find the name of the (child) table where
> > > the primary key of a (parent) table is a foreign key.
> > >
> >
> > There's some fairly standard syntax that covers this -and I don't have
 it to
> > hand. If you've been on the Backup and Recovery course, it's included
 in
> > the Oracle course notes for that. If no-one's posted anything else by
 this
> > evening, I'll have it for you then (it's currently 6am-ish).
> >
> > Regards
> > HJR
> >
> > > will appreciate any suggestions
> > >
>
Received on Tue Feb 06 2001 - 19:55:30 CST