Loading Tables with Reference constraints

SQL Question:- Awking a script to load a set of tables, most of them have reference constraints. Any one have a script to create a list of tables ordered so that referencial constraints are not violated. A simple list gives: - { load from t0250hmch_addid.unl insert into t0250hmch_addid; # 691: Missing key in referenced table for referential constraint (Loading . . . 847: Error in load file line 1. load from t0260pol_status.unl insert into t0260pol_status; . . . etc. } My initial look at this suggests and hour or two in the sys tables, but ole farther time has other desires as I am sure this will end up as a Stored Procedure. I am contracting on a site that has strict DBA and my usual trick of dropping all the constaints is not available. As a many year 4GL'er this would be a simple task, but this route is not available either. A damned SPL site, moving to Oracle as well. Will they never learn!! Mind, it keeps us contractors eating a crust or two, as all un sundry here no longer want to known Informix. Any kwiky solutions out there? Regards Ian A non metaphorical.

I have written a complex program to do this. If you are talking about a few tables, it's easy. Try it for 300 or so with 1500 relatioships constraints) and it gets pretty heavy. Lanny Ipellew <ipellew@pipemedia.co.uk> wrote in message news:7b72ta$b5n$1@news.xmission.com... > >SQL Question:- > >Awking a script to load a set of tables, most of them have reference >constraints. > >Any one have a script to create a list of tables ordered so that >referencial constraints are not violated. > >A simple list gives: - >{ > load from t0250hmch_addid.unl insert into t0250hmch_addid;> # 691: Missing key in referenced table for referential constraint >(Loading . . . > 847: Error in load file line 1.> load from t0260pol_status.unl insert into t0260pol_status;> . . . etc. >} > >My initial look at this suggests and hour or two in the sys tables, but >ole farther time has other desires as I am sure this will end up as a >Stored Procedure. > >I am contracting on a site that has strict DBA and my usual trick of >dropping all the constaints is not available. As a many year 4GL'er this > >would be a simple task, but this route is not available either. > >A damned SPL site, moving to Oracle as well. Will they never learn!! >Mind, >it keeps us contractors eating a crust or two, as all un sundry here no >longer want to known Informix. > >Any kwiky solutions out there? > >Regards >Ian A non metaphorical. > > >

I have a free script at URL: http://www.tc.umn.edu/~hause011 It gives a load order for tables with referential integrity constraints. -- --------------------------------------------------------- Steven Hauser email: hause011@tc.umn.edu URL: http://www.tc.umn.edu/~hause011 ---------------------------------------------------------

Ipellew (ipellew@pipemedia.co.uk) wrote: : Awking a script to load a set of tables, most of them have reference : constraints. : : Any one have a script to create a list of tables ordered so that : referencial constraints are not violated. Why don't you just go: SET CONSTRAINTS FOR table_name DISABLED; This disables the integrity constraints for the named table. You can turn 'em all off. Then you can load in peace, and set the constraints on again with: SET CONSTRAINTS FOR table_name ENABLED; Or check out the 'SET' entry in the SYNTAX GUIDE. There's lots of other stuff there too: like filtering the load. Of course, run a few queries to check that the data you've loaded actually is correct. Turning CONSTRAINTS on again doesn't do this.