Re: dropping foreign key constraints

The storage issue should only come into play if you're reenabling a primary key. This is because an index is implicitly created when a primary key is enabled, and the index requires a reasonable storage configuration. Foreign keys are stored in the data dictionary, but do not consume space, per se.

Rich

In article <7fitab$ao8_at_news.abbott.com>,   "Graham C Thornton" <graham.thornton_at_ln.ssw.abbott.com> wrote:
>
> Bear in mind that when you re-enable the constraints, you must specify
> the storage clause again, otherwise the constraint will be rebuilt
> in the user's default tablespace with 20Kb/20Kb/50%.
>
> A somewhat more challenging exercise :-)
>
> Graham
>
> Patrick Suppes wrote in message <371CEADF.A871877A_at_lucent.com>...
> >Jeff,
> >
> >I don't have a script to drop and recreate the foreign keys, but there is a
> >simpler
> >method to get there from here.
> >
> >Instead of dropping the constraints, just disable them.
> >
> >Syntax:
> >
> >ALTER TABLE schema.tablename DISABLE CONSTRAINT constraintname
> >
> >After you are done with your bashing, run
> >
> >ALTER TABLE schema.tablename ENABLE CONSTRAINT constraintname
> >
> >If you wait until after your reloads before enabling the constraints, you
> will
> >probably find the reloads run faster.
> >
> >As for the magic tool, run
> >
> > select 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME ||
> > ' DISABLE CONSTRAINT ' || constraint_name
> > from all_constraints
> >where owner = 'schema'
> > and table_name = 'tablename'
> > and constraint_type = 'R'
> >
> >The ENABLE variant is left as an exercise for the student.
> >
> >Patrick Suppes
> >
> >Jeff wrote:
> >
> >> I need nuke all the data in my the development instance of my Oracle 8
> server
> >> and use SQL*Loader to re-load accurate data from an outside non-Oracle
> system.
> >>
> >> My question is... does anyone have a script that drops all foreign key
> >> constraints in a database and then another script that can re-add them?
> I'm
> >> wondering if there's a way to do this by using the system catalog tables
> such
> >> as user_constraints ... maybe?
> >>
> >> --
> >> Jeff Avellanet
> >> Database Application Developer
> >> Washington, DC
> >>
> >> -----------== Posted via Deja News, The Discussion Network ==----------
> >> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
> >
>
>

--

Rich Goldkamp, Consultant, OCP		RJGoldka_at_dcss.com
Digital Consulting & Software Services	(504) 523-5005 x2736

-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/       Search, Read, Discuss, or Start Your Own