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/org/h2/table/MetaTable.java?r=3879#1299
... 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 field http://code.google.com/p/h2database/source/browse/trunk/h2/src/main/org/h2/constraint/ConstraintReferential.java?r=3879#63 (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.
