3 Replies Latest reply: Feb 5, 2014 5:21 PM by jgarry RSS

    Order of tables in Relational Database with Parent child relationship

    user512219

      Hi

       

      I have a Oracel database schema test1 which has 100+ tables with relational constraint for parent child relationship. There is a requirement to create a similar schema test2 for testing purpose and provide a scritpt to testing team to refresh the data from test1 to test2. Each time during refresh, All tables in test2 need to be deleted and load the data from test 1. While deleting child need to be deleted first and the parent but while loading parent need to be loaded first and then child.  Is there any Dictionary table avaialable to get all the tables in order for deletion and the reverse order for insertion.

       

      Export and import is a option for DBA but for QA people , these access cannot be given so end up in creating a script like this.

       

      Thanks

        • 1. Re: Order of tables in Relational Database with Parent child relationship
          JohnWatson

          Defer all your constraints before the delete and insert, set them back to immediate afterwards.

           

          This is why I always pre-create indexes as non-unique and then define my constraints as deferrable initially immediate.

          • 2. Re: Order of tables in Relational Database with Parent child relationship
            rp0428
            Export and import is a option for DBA but for QA people , these access cannot be given so end up in creating a script like this

            TERRIBLE IDEA! Don't do that!

             

            You should ask management to approve having the DBAs use export and import.

             

            The process you suggest of using DELETE and INSERT has a MAJOR flaws that can very quickly cause some MAJOR problems.

             

            That flaw is: there will be NO guarantee that the tables, indexes and other constraints on those 'test2' schema objects will be identical to the original objects in the 'test1' schema.

             

            The chances of both sets of objects being identical for any length of time is SLIM and NONE. If even a singlel object is different it could potentially invalidate your entire set of tests and wind up just wasting everyone's time.

             

            1. a table column in test1 might have been changed from VARCHAR2(30) to VARCHAR2(40) and have data too big to fit in the old 'test2' table. That will break your DELETE/INSERT process.

             

            2. one or more test1 tables may have had new columns added or had columns dropped.  That will break your DELETE/INSERT process.

             

            3. one or more test1 tables may have had column constraints or defaults changed. That can break your DELETE/INSERT process.

             

            It makes NO sense to waste your time writing a complex set of scripts/code to avoid the one constraint problem you mentioned only to have all of that time wasted if a developer makes even ONE simple, and needed, change to a single table.

             

            There is NO WAY you can write a bullet-proof set of scripts/code to try to detect all of those possible issues.

             

            Worse - when your DELETE/INSERT process fails in the middle it will very very time consuming to figure out how to fix the problem and then manually finish the process of loading the remaining tables.

             

            The process will break when it encounters the first problem. If you then just fix that problem and restart from the beginning the process could then break again on the next problem that you didn't know existed yet.

             

            Export/Import avoids ALL of those issues and also avoids ALL of that nonsense of writing a set of scripts/code that can't guarantee to do the job anyway.

             

            The only 'script' you should write is one that will DROP all of the objects in 'test2' so that the import is guaranteed to create the proper versions of them.when the import is done.

             

            Please - ask management to rethink that plan - it is DOOMED to fail.

            • 3. Re: Order of tables in Relational Database with Parent child relationship
              jgarry

              I'd go one step further than rp, and drop/recreate the schema.  Why?  I've seen odd things happen with triggers when some old session remained connected while impdp replaces a table.  Never quite was able to figure out exactly what was happening, though, even recompiling with no errors and flushing shared pool wouldn't fix it.  Some poor QA guy would be driven nuts.  I could scarcely believe it myself (10.2.0.4 with vendor-written trigger that emulates what SQL Server computed columns do).