RE: Sort table names and maintain referential integrity

Depending on how much data there is he might hit long transactions though... >===== Original Message From kagel@bloomberg.net ===== >domenm@my-deja.com wrote: >> >> 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... > >Ahh, now I know how to help you! OK, assuming that your database has >transactions (ie is logged), if you execute the following the -691 errors >will vanish: > >SET CONSTRAINTS DEFERRED; >BEGIN WORK; >..... insert/update/delete ..... >COMMIT WORK; > >The SET CONSTRAINTS DEFERRED will defer constraint checking until COMMIT >time when hopefully you will have inserted the parent records to satisfy >all dependencies. Of course if your database(s) does not have >transactions you are stuck, and IMNSHO are getting what you deserve. All >databases should be logged! > >See what I mean about posting the problem and NOT asking how to make a >particular solution work? > >Art S. Kagel > >> 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/ ------------------------------------------------------------