Possible bug in code generator regarding foreign keys

[Jens Teglhus Møller]

Hi

While trying to setup partitioned tables, I have noticed that the code generator seem to have a bug or feature where it does not alway generate all the foreign keys. 

When running the code generator on the following H2 database:

create table parent (
  id int generated by default as identity,
  partition_id tinyint not null,
  constraint pk_parent primary key (id, partition_id)
);

create table child1 (
  id int generated by default as identity,
  parent_id int not null,
  partition_id tinyint not null,
  constraint pk_child1 primary key (id),
  constraint fk_child1_parent foreign key (parent_id, partition_id) references parent (id, partition_id)
);

create table child2 (
  id int generated by default as identity,
  parent_id int not null,
  constraint pk_child2 primary key (id),

  -- this fk does not cover the parents pk 
  constraint fk_child2_parent foreign key (parent_id) references parent (id)
);

The foreign key for child2 is not generated (note that this fk is a bit funky, since it only references part of the parent pk).

The only generator log output regarding foreign keys is this:

[INFO] Adding foreign key       : FK_CHILD1_PARENT (PUBLIC.CHILD1.PARENT_ID) referencing PK_PARENT (PUBLIC.PARENT.ID)

[INFO] Adding foreign key       : FK_CHILD1_PARENT (PUBLIC.CHILD1.PARTITION_ID) referencing PK_PARENT (PUBLIC.PARENT.PARTITION_ID)

I can push an example to github or supply generator log. I tested with jooq v1.15.2 and initially discovered it on v3.14.13.

Best regards Jens

[Lukas Eder]

Hi Jens,

The foreign key for child2 is not generated (note that this fk is a bit funky, since it only references part of the parent pk).

But why would you do that? I mean, GENERATED BY DEFAULT is by no means a unique constraint. There could be duplicate id values in the parent table. Things would probably work if you added a UNIQUE constraint. Or better yet, add the partition_id also to child2, because after all, the key is composite, so why not fix the schema?

--
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 jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/bd40cd8d-201a-404b-88db-e7bfa262a3e7n%40googlegroups.com.

[Jens Teglhus Møller]

Hi Lukas

I will fix the schema, I was just bitten because the jooq generator silently ignores the relationship and it took me some time to figure out.

The construct seems legal SQL wise so should jooq generate the foreign key or not?

Best regards Jens

[Lukas Eder]

Hi Jens,

So, after reverse engineering the H2 information_schema, it can be seen that H2 creates an implicit unique index on the identity column. So, there *is* a uniqueness guarantee after all.

However, what is being referenced is not a constraint, but an index. A not-yet-released version of H2 is supposed to provide this information in the information_schema, but the current version does not yet provide it:

https://github.com/h2database/h2database/issues/466

Related:

https://github.com/jOOQ/jOOQ/issues/11609

So, this issue should be resolved once the next version of H2 after 1.4.200 is out.

You can always add synthetic constraints if your schema is missing the meta data, or if your database cannot produce it:

https://www.jooq.org/doc/latest/manual/code-generation/codegen-advanced/codegen-config-database/codegen-database-synthetic-objects/codegen-database-synthetic-fks/

I hope this helps,

Lukas

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/b1479834-edd9-493f-b8db-569dea896b39n%40googlegroups.com.

[Jens Teglhus Møller]

Hi Lukas

Thanks for the info. So this is actually an H2 bug. Impressive memory you have.

I will make my own data model less broken so I won't run into the issue.

Best regards Jens