New versions: Dev (3.14)

Overlapping embeddable types

Applies to ❌ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

A previous section explained how embeddable types work in general. In some cases, there's a risk of overlapping embeddable types, which can mainly happen when using embedded keys, but also in some other cases.

For example, when defining an embeddable for each one of these UNIQUE constraints:

CREATE TABLE order_item (
  order_id BIGINT NOT NULL REFERENCES order
  product_id BIGINT NOT NULL REFERENCES product,
  item_no INT NOT NULL,
  quantity INT NOT NULL,
  
  -- Each order_item has a unique-per-order item_no, which acts as a sequential number
  CONSTRAINT pk_order_item PRIMARY KEY (order_id, item_no),
  
  -- Each product can only have one order_item
  CONSTRAINT uk_order_item UNIQUE (order_id, product_id)
);

The two UNIQUE constraints overlap. If they are represented by an org.jooq.EmbeddableRecord, each (e.g. because of using the embedded keys feature), then both of the embeddable types will reference the ORDER_ID column. jOOQ will make sure that the ORDER_ID column is not generated twice in SQL statements, where this is forbidden, e.g. in INSERT statements:

INSERT INTO order_item (
  order_id,
  product_id, 
  item_no,
  quantity
)
VALUES (
  12,
  15,
  1,
  10
);
create.insertInto(ORDER_ITEM)
      .columns(
        ORDER_ITEM.PK_ORDER_ITEM, 
        ORDER_ITEM.UK_ORDER_ITEM, 
        ORDER_ITEM.QUANTITY
      )
      .values(
        new PkOrderItemRecord(12L, 15L), 
        new UkOrderItemRecord(15L, 1), 
        1
      )
      .execute();

Despite the value 15L having been provided twice, it is produced in the generated SQL query only once (the second copy of the value is ignored).

The jOOQ Logo