Hi Ryan,
It took me a while now, to reconstruct why the jOOQ query needs to join
CONSTRAINTS, even if CROSS_REFERENCES seems to hold all the required
information.
CROSS_REFERENCES.PK_NAME is the name of the referenced unique index
(CONSTRAINTS.UNIQUE_INDEX_NAME), not the primary key constraint name
(CONSTRAINTS.CONSTRAINT_NAME). Now, CONSTRAINTS.UNIQUE_INDEX_NAME seems to
be abused for both primary/unique key definitions (defining index), and
foreign key definitions (referenced index), in the same table.
jOOQ-meta's model really needs the primary/unique key constraint name,
internally, to create a link between FK and PK/UK. So the join condition
should be correct. In fact, it is probably incomplete in a way that I
should add
AND "INFORMATION_SCHEMA"."CONSTRAINTS"."CONSTRAINT_TYPE" in ('UNIQUE',
'PRIMARY KEY')
Now I wouldn't exclude that there is a configuration where the inner join
excludes results due to either:
1. An incomplete understanding on that CROSS_REFERENCES.PK_NAME =
CONSTRAINTS.UNIQUE_INDEX_NAME relation on my side
2. A bug in H2. I've reported subtle bugs in the H2 INFORMATION_SCHEMA
before, so I wouldn't exclude that...
> I can attach full output of CROSS_REFERENCE table, CONSTRAINTS table and
my DDL for a couple of table if you like
Yes, please. I think this needs further investigation
Cheers
Lukas
2013/2/20 Ryan How <[email protected]>
> 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.
>
>
>
--
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.
