Re: dropping foreign key constraints

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
Received on Tue Apr 20 1999 - 16:00:15 CDT