Whoever might be interested in a workaround here: I've written some h2acks ( :D ) providing the missing feature as a SQL procedure. https://jadoth.svn.sourceforge.net/svnroot/jadoth/H2acks/src/net/jadoth/h2acks/SQLFunctions.java (sourceforge home: http://sourceforge.net/projects/jadoth/)
This solution is sufficient for me for the time being, of course I'd prefer to have a proper fix for the problem in some future version of H2. On Dec 10, 1:46 pm, Paigan Jadoth <[email protected]> wrote: > I did a little research in the H2 code base and found that the column > could be added very easily to the CROSS_REFERENCES table, I think. > Somewhere around > here...http://code.google.com/p/h2database/source/browse/trunk/h2/src/main/o... > > ... there would just have to a line... > Index index = ref.getIndex(); > > ... and then below in the add(...) call an additional entry > //FKINDEX_NAME (or whatever) > identifier(index.getName()), > > Of course ConstraintReferential#index would require a getter to be > created for that > fieldhttp://code.google.com/p/h2database/source/browse/trunk/h2/src/main/o... > (is there a particular reason that the FK instance's index is not > gettable?) > And the column name would have to be registered (http:// > code.google.com/p/h2database/source/browse/trunk/h2/src/main/org/h2/ > table/MetaTable.java?r=3879#392) > > I surely don't have an overview over all the internals of H2, > nevertheless the code structure looks pretty clean so that such a > change should be no problem. Also existing usage of the I_S.C_R table > should not be influenced because nothing existing would change or be > removed. > > It would be very helpful if such an extension would be considered for > a future version, because currently the information is really > missing :-(. > Maybe I can build H2 on my own during the holidays to test the change > myself and help reduce the effort :) > > On Dec 8, 6:08 pm, Paigan Jadoth <[email protected]> wrote: > > > > > > > > > I think maybe I described the problem to complicated or with too many > > details at once. > > > The issue simply is: > > How can I query the information schema for the names of the FK-table > > indices used by all foreign keys if potentially multiple FKs use the > > same FK-table index? > > > Thank you for answers. > > > On Dec 6, 7:49 pm, Paigan Jadoth <[email protected]> wrote: > > > > 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. > > > Works all pretty well, except for one problem: > > > If more than one FOREIGN KEY use the same FKTABLE index, only the last > > > association can be read from the I_S while all previous associations > > > get suppressed. > > > This means important information is lost, which in turn leads to > > > errors (which is not desirable). > > > (see SQL batch below for an example) > > > > The reason is that INFORMATION_SCHEMA.CROSS_REFERENCES is missing a > > > column FKTABLE_INDEX which provides the name of the associated index > > > for the particular FK. > > > The backtrack information provided by > > > INFORMATION_SCHEMA.INDEXES.CONSTRAINT_NAME sadly is not enough, > > > because it's only one value to represent a 1-to-n relationship (1 > > > FKTable-index can be referenced by n FKs). > > > Hence the C_R entry itself must show which index it is linked to (just > > > like the already existing column PK_NAME does for the target table > > > unique constraint). > > > > Is there another way of retrieving the missing information? > > > Is there a chance for the missing column to be added in a future > > > version of H2? > > > > Thanks for answers :-) > > > > Here is an example that demonstrates the problem: > > > > -- DDL > > > CREATE TABLE A(a1 INT, a2 INT); > > > CREATE TABLE B(b1 INT, b2 INT); > > > CREATE TABLE C(c1 INT, c2 INT); > > > CREATE INDEX idx1 ON B(b1, b2); > > > CREATE INDEX idx2 ON B(b1, b2); > > > ALTER TABLE B ADD CONSTRAINT FK_b_a FOREIGN KEY (b1, b2) REFERENCES > > > A(a1, a2); > > > ALTER TABLE B ADD CONSTRAINT FK_b_c FOREIGN KEY (b1, b2) REFERENCES > > > C(c1, c2); > > > > -- I_S query > > > SELECT > > > CRF.FKTABLE_NAME, > > > CRF.FK_NAME, > > > CRF.FKCOLUMN_NAME, > > > IDX.INDEX_NAME, > > > IDX.COLUMN_NAME AS IDXCOLUMN_NAME > > > FROM INFORMATION_SCHEMA.CROSS_REFERENCES CRF > > > LEFT JOIN INFORMATION_SCHEMA.INDEXES IDX ON IDX.NON_UNIQUE = TRUE > > > AND IDX.TABLE_CATALOG = CRF.FKTABLE_CATALOG > > > AND IDX.TABLE_SCHEMA = CRF.FKTABLE_SCHEMA > > > AND IDX.TABLE_NAME = CRF.FKTABLE_NAME > > > AND IDX.COLUMN_NAME = CRF.FKCOLUMN_NAME > > > AND IDX.CONSTRAINT_NAME = CRF.FK_NAME -- 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.
