Sort table names and maintain referential integrity

Hi! I have very tricky problem here. I have a short list of tables and I want to sort their names in a way to maintain their referential integrity. For example: table1 is master of table3 table3 is master of table4, table5 and table6 table2 is also master of table3 table5 is master of table7 ... So, is there any known algorithms to sort the tables in the right way? Any help will sincerely be appreciaed! Domen Sent via Deja.com http://www.deja.com/ Before you buy.

Use the sysreferences table. Art S. Kagel domenm@my-deja.com wrote: > > Hi! > I have very tricky problem here. I have a short list of tables and I > want to sort their names in a way to maintain their referential > integrity. For example: > table1 is master of table3 > table3 is master of table4, table5 and table6 > table2 is also master of table3 > table5 is master of table7 ... > > So, is there any known algorithms to sort the tables in the right way? > > Any help will sincerely be appreciaed! > Domen > > Sent via Deja.com http://www.deja.com/ > Before you buy.

Hi! Thanks for your quick response! Maybe I didn't give you enough data. I used sysreferences table, but here comes the tricky part. What if the original list of table names readed from database comes in the following order: 1. table4 2. table5 3. table6 4. table1 5. table3 6. table7 7. table2 Table1 is in front of table3, but they are all master tables. So the problem is in table1 which should be inserted before table3 (becouse table1 is master of table3). And that's the algorithm I'm searching for. Thanx any way! Domen ___________________________________________ In article <39639739.575293A7@bloomberg.net>, kagel@bloomberg.net wrote: > Use the sysreferences table. > > Art S. Kagel > > domenm@my-deja.com wrote: > > > > Hi! > > I have very tricky problem here. I have a short list of tables and I > > want to sort their names in a way to maintain their referential > > integrity. For example: > > table1 is master of table3 > > table3 is master of table4, table5 and table6 > > table2 is also master of table3 > > table5 is master of table7 ... > > > > So, is there any known algorithms to sort the tables in the right way? > > > > Any help will sincerely be appreciaed! > > Domen > > > > Sent via Deja.com http://www.deja.com/ > > Before you buy. > Sent via Deja.com http://www.deja.com/ Before you buy.

You could write a Stored Procedure (using sysconstraints & sysreferences) to derive the order. However, if your purpose is to successfully enable all referential integrity on data, say, that you've copied over from a "good" source, there's another tack you can take. 1. Disable all constraints on all tables in your list (this also helps your load). 2. Load data 3. Enable all constraints. Step 1 and 3 are, as you know, not as simple as they seem - they need to be done in the correct order. However, based on the observation that the cost of enabling an already enabled constraint (and vice-versa) is negligible, you could make multiple attempts to set the mode of the constraints on all the related tables until an attempt succeeds on all tables. Working : In the first pass all "independent" tables' constraints get enabled. In the second pass, tables that depended on the ones that succeeded in the first pass succeed. And so on. The script below uses this idea. You will need to modify it to suit your requirements. #!/bin/ksh # Enables/Disables indexes, constraints if [ $# != 2 ]; then echo Call with "<dbname>" "<enable|disable>" exit fi DB=$1 MODE=$2 MAX_ATTEMPTS=10 TABLIST=`echo "select tabname[1,18] from systables where tabname matches 'so_*'" |\\ dbaccess $DB 2>/dev/null | sed '1,4'd` error_no=1 tries=0 while [ $error_no != 0 ] && [ $tries -lt $MAX_ATTEMPTS ] do error_no=0 tries=`expr $tries + 1` echo Attempt $tries ... for table in $TABLIST do echo Attempting to $MODE $table objects... dbaccess $DB << eo_db set indexes, constraints for $table $MODE; eo_db if [ $? != 0 ]; then error_no=1 fi done done exit $error_no Rudy domenm@my-deja.com wrote: > Hi! > I have very tricky problem here. I have a short list of tables and I > want to sort their names in a way to maintain their referential > integrity. For example: > table1 is master of table3 > table3 is master of table4, table5 and table6 > table2 is also master of table3 > table5 is master of table7 ... > > So, is there any known algorithms to sort the tables in the right way? > > Any help will sincerely be appreciaed! > Domen > > Sent via Deja.com http://www.deja.com/ > Before you buy.

OK, let's start over. Tell us what it is you are trying to accomplish and we'll tell you how to get the job done. As it is you are stuck on how to do it a particular way when there may be another easier solution. For example, if you are trying to create a schema that you can use to recreate the database and want the tables created so that FOREIGN KEY clauses in the CREATE TABLE statement do not fail, the simple solution is to add the FOREIGN KEY constraints as separate ALTER TABLE ADD CONSTRAINT FORIEGN KEY statements AFTER all the tables have been created. This is what myschema and even dbschema do to get around the problem since any attempt to calculate the relationships beforehand will fail if there are mutually referencing tables or other circular relationships. Anyway, post your intent, not your solution, and we will try to help. Art S. Kagel domenm@my-deja.com wrote: > > Hi! > Thanks for your quick response! > > Maybe I didn't give you enough data. > I used sysreferences table, but here comes the tricky part. > What if the original list of table names > readed from database comes in the following order: > 1. table4 > 2. table5 > 3. table6 > 4. table1 > 5. table3 > 6. table7 > 7. table2 > > Table1 is in front of table3, but they are all master tables. So the > problem is in table1 which should be inserted before table3 (becouse > table1 is master of table3). And that's the algorithm I'm searching for. > > Thanx any way! > Domen > > ___________________________________________ > > In article <39639739.575293A7@bloomberg.net>, > kagel@bloomberg.net wrote: > > Use the sysreferences table. > > > > Art S. Kagel > > > > domenm@my-deja.com wrote: > > > > > > Hi! > > > I have very tricky problem here. I have a short list of tables and I > > > want to sort their names in a way to maintain their referential > > > integrity. For example: > > > table1 is master of table3 > > > table3 is master of table4, table5 and table6 > > > table2 is also master of table3 > > > table5 is master of table7 ... > > > > > > So, is there any known algorithms to sort the tables in the right > way? > > > > > > Any help will sincerely be appreciaed! > > > Domen > > > > > > Sent via Deja.com http://www.deja.com/ > > > Before you buy. > > > > Sent via Deja.com http://www.deja.com/ > Before you buy.

Hi! What I'm trying to do is to get a sorted list of table names representing the right referential integrity. This list is then used by my application to insert (load) data into those tables in the database. The main functionality of application is to synchronize data among databases. So, application finds differences on some tables, generates adequate SQL sentences and tries to execute those sentences on the comparative side (tables). By default the table names are sorted alphabetically and when I'm lucky the referential integrity is already correct. But mostly the application tries to insert some data into a slave table and master table data is not existing yet. As a result I get an error -691 (Missing key in referenced table for referential constraint constraint-name). So, generaly the problem seems to be very simple, but... Thanks for all responses. Domen -------------------------------------------------------- In article <3964A780.5DCB9DDE@bloomberg.net>, kagel@bloomberg.net wrote: > OK, let's start over. Tell us what it is you are trying to accomplish and > we'll tell you how to get the job done. As it is you are stuck on how to > do it a particular way when there may be another easier solution. > > For example, if you are trying to create a schema that you can use to > recreate the database and want the tables created so that FOREIGN KEY > clauses in the CREATE TABLE statement do not fail, the simple solution is > to add the FOREIGN KEY constraints as separate ALTER TABLE ADD CONSTRAINT > FORIEGN KEY statements AFTER all the tables have been created. This is > what myschema and even dbschema do to get around the problem since any > attempt to calculate the relationships beforehand will fail if there are > mutually referencing tables or other circular relationships. > > Anyway, post your intent, not your solution, and we will try to help. > > Art S. Kagel > > domenm@my-deja.com wrote: > > > > Hi! > > Thanks for your quick response! > > > > Maybe I didn't give you enough data. > > I used sysreferences table, but here comes the tricky part. > > What if the original list of table names > > readed from database comes in the following order: > > 1. table4 > > 2. table5 > > 3. table6 > > 4. table1 > > 5. table3 > > 6. table7 > > 7. table2 > > > > Table1 is in front of table3, but they are all master tables. So the > > problem is in table1 which should be inserted before table3 (becouse > > table1 is master of table3). And that's the algorithm I'm searching for. > > > > Thanx any way! > > Domen > > > > ___________________________________________ > > > > In article <39639739.575293A7@bloomberg.net>, > > kagel@bloomberg.net wrote: > > > Use the sysreferences table. > > > > > > Art S. Kagel > > > > > > domenm@my-deja.com wrote: > > > > > > > > Hi! > > > > I have very tricky problem here. I have a short list of tables and I > > > > want to sort their names in a way to maintain their referential > > > > integrity. For example: > > > > table1 is master of table3 > > > > table3 is master of table4, table5 and table6 > > > > table2 is also master of table3 > > > > table5 is master of table7 ... > > > > > > > > So, is there any known algorithms to sort the tables in the right > > way? > > > > > > > > Any help will sincerely be appreciaed! > > > > Domen > > > > > > > > Sent via Deja.com http://www.deja.com/ > > > > Before you buy. > > > > > > > Sent via Deja.com http://www.deja.com/ > > Before you buy. > Sent via Deja.com http://www.deja.com/ Before you buy.

domenm@my-deja.com wrote: > I have very tricky problem here. I have a short list of tables and I > want to sort their names in a way to maintain their referential > integrity. For example: > table1 is master of table3 > table3 is master of table4, table5 and table6 > table2 is also master of table3 > table5 is master of table7 ... > > So, is there any known algorithms to sort the tables in the right way? This question was also asked in comp.databases.theory, where I gave the answer which follows: It's called a topological sort. On Unix, the tsort program implements a topological sort. Knuth describes the algorithm in Vol 1 (section 2.2.3 in my 2nd Edn). -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"

I have a topological sort implemented in dbaccess and awk with the sort code adapted from the AWK book by Aho Wein.., Kernigan (guys who wrote awk). It is somewhere in the IIUG archives or on http://www.sofbot.com/ ref_load_ord is the script name. -- --------------------------------------------------------- Steven Hauser email: hause011@tc.umn.edu URL: http://www.tc.umn.edu/~hause011 ---------------------------------------------------------