Available in versions: Dev (3.19) | Latest (3.18) | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9

Updatable Primary Keys

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

In most database design guidelines, primary key values are expected to never change - an assumption that is essential to a normalised database.

As always, there are exceptions to these rules, and users may wish to allow for updatable primary key values in the updatable records feature (note: any value can always be updated through ordinary update statements). An example:

AuthorRecord author =
DSL.using(configuration) // This configuration will be attached to any record produced by the below query.
   .selectFrom(AUTHOR)
   .where(AUTHOR.ID.eq(1))
   .fetchOne();

author.setId(2);
author.store(); // The behaviour of this store call is governed by the updatablePrimaryKeys flag

The above store call depends on the value of the updatablePrimaryKeys flag:

  • false (the default): Since immutability of primary keys is assumed, the store call will create a new record (a copy) with the new primary key value.
  • true: Since mutablity of primary keys is allowed, the store call will change the primary key value from 1 to 2.

Programmatic configuration

Settings settings = new Settings()
    .withUpdatablePrimaryKeys(true); // Defaults to false

XML configuration

<settings xmlns="http://www.jooq.org/xsd/jooq-runtime-3.9.0.xsd">
  <updatablePrimaryKeys>true</updatablePrimaryKeys>
</settings>

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo