Re: Constraint dependencies

From: <Diana_Duncan_at_ttpartners.com>
Date: Wed, 02 May 2001 11:22:25 -0700
Message-ID: <F001.002F73A2.20010502070023@fatcity.com>

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.

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)
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: Diana_Duncan_at_ttpartners.com


                                                                                       
                            
                    "Yttri, Lisa"                                                      
                            
                    <lisa.yttri_at_c        To:     Multiple recipients of list ORACLE-L 
<ORACLE-L_at_fatcity.com>       
                    nh.com>              cc:                                           
                            
                    Sent by:             Fax to:                                       
                            
                    root_at_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: Diana_Duncan_at_ttpartners.com

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: ListGuru_at_fatcity.com (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). Received on Wed May 02 2001 - 13:22:25 CDT