Hi Lukas,
This query seems incorrect, or H2 has incorrect information in the
INFORMATION_SCHEMA. I'm using version 1.3.170
It is omitting records because of these clauses in the join
AND "INFORMATION_SCHEMA"."CROSS_REFERENCES"."PKTABLE_NAME" =
"INFORMATION_SCHEMA"."CONSTRAINTS"."TABLE_NAME"
AND "INFORMATION_SCHEMA"."CROSS_REFERENCES"."PKTABLE_SCHEMA" =
"INFORMATION_SCHEMA"."CONSTRAINTS"."TABLE_SCHEMA")
These should be
AND "INFORMATION_SCHEMA"."CROSS_REFERENCES"."FKTABLE_NAME" =
"INFORMATION_SCHEMA"."CONSTRAINTS"."TABLE_NAME"
AND "INFORMATION_SCHEMA"."CROSS_REFERENCES"."FKTABLE_SCHEMA" =
"INFORMATION_SCHEMA"."CONSTRAINTS"."TABLE_SCHEMA")
Then the query works, except it returns some duplicate rows if the same
foreign key is referenced several times for the 1 table. eg. COLUMN1
REFERENCES OTHER_TABLE, COLUMN2 REFERENCES OTHER_TABLE
So adding
AND "INFORMATION_SCHEMA"."CROSS_REFERENCES"."FK_NAME" =
"INFORMATION_SCHEMA"."CONSTRAINTS"."CONSTRAINT_NAME"
Fixes that.
Final Query
SELECT "INFORMATION_SCHEMA"."CROSS_REFERENCES"."FK_NAME",
"INFORMATION_SCHEMA"."CROSS_REFERENCES"."FKTABLE_NAME",
"INFORMATION_SCHEMA"."CROSS_REFERENCES"."FKTABLE_SCHEMA",
"INFORMATION_SCHEMA"."CROSS_REFERENCES"."FKCOLUMN_NAME",
"INFORMATION_SCHEMA"."CONSTRAINTS"."CONSTRAINT_NAME",
"INFORMATION_SCHEMA"."CONSTRAINTS"."CONSTRAINT_SCHEMA"
FROM "INFORMATION_SCHEMA"."CROSS_REFERENCES"
JOIN "INFORMATION_SCHEMA"."CONSTRAINTS"
ON ("INFORMATION_SCHEMA"."CROSS_REFERENCES"."PK_NAME" =
"INFORMATION_SCHEMA"."CONSTRAINTS"."UNIQUE_INDEX_NAME"
AND "INFORMATION_SCHEMA"."CROSS_REFERENCES"."FK_NAME" =
"INFORMATION_SCHEMA"."CONSTRAINTS"."CONSTRAINT_NAME"
AND "INFORMATION_SCHEMA"."CROSS_REFERENCES"."FKTABLE_NAME" =
"INFORMATION_SCHEMA"."CONSTRAINTS"."TABLE_NAME"
AND "INFORMATION_SCHEMA"."CROSS_REFERENCES"."FKTABLE_SCHEMA" =
"INFORMATION_SCHEMA"."CONSTRAINTS"."TABLE_SCHEMA")
WHERE "INFORMATION_SCHEMA"."CROSS_REFERENCES"."FKTABLE_SCHEMA" IN
('PUBLIC')
ORDER BY "INFORMATION_SCHEMA"."CROSS_REFERENCES"."FKTABLE_SCHEMA" ASC,
"INFORMATION_SCHEMA"."CROSS_REFERENCES"."FK_NAME" ASC,
"INFORMATION_SCHEMA"."CROSS_REFERENCES"."ORDINAL_POSITION" ASC
Does this look suitable to you?
I can attach full output of CROSS_REFERENCE table, CONSTRAINTS table and
my DDL for a couple of table if you like.
Thanks, Ryan
PS. Does this reply need to be moderated?, I have my gmail forwarding to
another account, so when I reply it thinks it is someone else.
--
You received this message because you are subscribed to the Google Groups "jOOQ User
Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/groups/opt_out.
