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.
