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.
