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
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) );
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 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).