Lisa,
This is unfortunately quite difficult -- and if you have any circular
dependencies it's pretty much impossible. Otherwise, what you need to do
is follow the dependencies in the dba_constraints view.
For the drop order.
1) Drop all of the tables for which their PK constraint is not
referenced by any FK constraints
select constraint_name
from user_constraints c1
where not exists (select constraint_name
from user_constraints c2
where c2.r_constraint_name = c1.constraint_name)
2) Now that you've dropped the first group, you can do the same query
again. If you are just creating a drop script, you'll need to keep track
of the ones you've "dropped" so you can restrict the query.
The creation order is simpler -- you need to create all of the primary key
constraints first, then the foreign keys.
Diana Duncan
TITAN Technology Partners
One Copley Parkway, Ste 540
Morrisville, NC 27560
P: 919.466.7337
F: 919.466.7427
E: [EMAIL PROTECTED]
"Yttri, Lisa"
<lisa.yttri@c To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
nh.com> cc:
Sent by: Fax to:
root@fatcity. Subject: Constraint dependencies
com
05/01/2001
05:50 PM
Please
respond to
ORACLE-L
Hi -
When dropping and/or re-applying constraints for an entire schema, is there
a dictionary table that can be queried (or some other way) to determine
constraint dependencies - or do you just keep running the script until you
do not get those errors? I would like to be able to identify the order
that the constraints should be dropped (or added) so that I can write a
script to be automated ( for example, to refresh test data from
production).
If anyone has any good ideas on how to do this, I'd really be interested in
hearing from you.
Thanks -
Lisa
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
