Hi
Sorry I didn't look at this for so long. Firstly, there is something wrong with your example. IDX1 and IDX2 are not being used at all to enforce those foreign keys. If those indexes were on table A and table C, then the foreign key logic might use them as an optimisation to check those constraints, but it is not required to do so. Secondly, you can reconstruct constraints and indexes by looking at the schema CONSTRAINTS and INDEXES tables. You don't need to involve CROSS_REFERENCES. Regards, Noel Grandin On Fri, Dec 16, 2011 at 19:36, Paigan Jadoth <[email protected]> wrote: > Hi > > and thanks for reading all that stuff :). > > The problem in principle here is very similar to > http://groups.google.com/group/h2-database/t/5a3a1eccac322abc, so I > can cut most of the blabla this time. > It simply is about architecture: SCRIPT just dumps a structure- > reproduction DDL script. But what is required for proper architecture > is for the application to "understand" the DBMS's structures (= read > the meta data and build the structure abstractly from it to have > something to work on). Oh btw. still SCRIPT is a cool function, no > doubt :D. > > To your question: > It's for potentially all DBMS (and an earlier version of my lib is > already in production use with adaptors for multiple DBMS), but H2 is > the first to be used with the extended features of handling > constraints etc. > Still I can already tell from experience: Some of them will provide > all the required data, some won't. As usual. For the bulky ancient > DBMS, I don't even bother to talk to their support about such things > but just say "not possible, blame the ancient DBMS". H2 in contrast is > modern and cleanly written enough to actually fix stuff like that, so > I try it :). > > Also H2 shouldn't hide behind the deficiencies of ancient DBMSs. As > far as I understand it, H2 is meant to provide a better alternative, > not just say "well it doesn't work but the others don't, either". > > > The feature missing in H2 is: > As one INDEX can be used by n FOREIGN KEYS, it's not sufficient to > provide the name of one FK that uses the index, but of ALL FKs using > the index. > The easiest and cleanest way to do so is to extend the > I_S.CROSS_REFERENCES table with a column FKTABLE_INDEX_NAME (or so) > that provides the information "this FK uses that INDEX". > Similar to the already existing column PK_NAME. > > As far as I could see into H2's internal structures and the working > workaround procedure, this should be very easily implemented. > Again of course only with low priority, fully understandable. > > > Regards > > > > On Dec 16, 11:05 am, Thomas Mueller <[email protected]> > wrote: >> Hi, >> >> > I am trying to automatically and generically read the structure of a >> > DB from the INFORMATION_SCHEMA to build up a data structure >> > representing the DB. >> >> Is this just for H2 or for other databases as well? >> >> Do other databases provide all the data you need? If yes, what feature >> is missing in H2? >> >> If it's just for H2, I suggest to use the SCRIPT statement. >> >> Regards, >> Thomas > > -- > You received this message because you are subscribed to the Google Groups "H2 > Database" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/h2-database?hl=en. > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
