RE: Sort table names and maintain referential integrity

If you get circular referencing then there is no "order" you can load in without disabling constraints, or by throwing stuff into temp tables, and figuring out records which go together. If you there are no circular references you could do something like the following. 1. Throw all the references into a temp table with parent_tabid and child_tabid. 2. Find a tables which is not a child in any of the entries 3. Load the table. 4. Delete all entries which have this table as the parent. 5. goto 2 Hope this helps, Will P.S. you could skip step 3 to build the order and then load afterwards. P.P.S. I am aware it is more efficient to grab all the tables which are not children at once, but felt that was more cumbersome to explain. >===== Original Message From domenm@my-deja.com ===== >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. ------------------------------------------------------------ This e-mail has been sent to you courtesy of OperaMail, as a free service from Opera Software, makers of the award-winning Web Browser, Opera. Visit us at http://www.opera.com/ or our portal at: http://www.myopera.com/ Your free e-mail account is waiting at: http://www.operamail.com/ ------------------------------------------------------------