Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14

This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.

Synthetic unique keys

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

jOOQ's code generator recognises unique keys that are declared and reported as such by the database. But some databases don't report all keys, or some tables don't have them enabled, or sometimes, a view is a 1:1 representation of an underlying table, but it doesn't expose the key information. In these cases, this regular expression can match all columns that users wish to "pretend" are part of such a unique key. If a composite synthetic unique key is desired, the regular expression should match all columns of that table that are part of the unique key. For example, a composite synthetic unique key consists of (COLUMN1, COLUMN2) in table SCHEMA.TABLE:

XML (standalone and maven)

Programmatic

Gradle (Kotlin)

Gradle (Groovy)

Gradle (third party)

<configuration>
  <generator>
    <database>
      <syntheticObjects>
        <uniqueKeys>
          <uniqueKey>

            <!-- Optional name of the unique key, if tables matches only a single table. -->
            <name>UK_TABLE</name>

            <!-- Optional regular expression matching all tables that have this unique key. -->
            <tables>SCHEMA\.TABLE</tables>

            <!-- List multiple fields in the key order -->
            <fields>
              <field>COLUMN1</field>
              <field>COLUMN2</field>
            </fields>
          </uniqueKey>
        </uniqueKeys>
      </syntheticObjects>
    </database>
  </generator>
</configuration>

See the configuration XSD, standalone code generation, and maven code generation for more details.

new org.jooq.meta.jaxb.Configuration()
  .withGenerator(new Generator()
    .withDatabase(new Database()
      .withSyntheticObjects(new SyntheticObjectsType()
        .withUniqueKeys(
          new SyntheticUniqueKeyType()

            // Optional name of the unique key, if tables matches only a single table.
            .withName("UK_TABLE")

            // Optional regular expression matching all tables that have this unique key.
            .withTables("SCHEMA\\.TABLE")

            // List multiple fields in the key order
            .withFields(
              "COLUMN1",
              "COLUMN2"
            )
        )
      )
    )
  )

See the configuration XSD and programmatic code generation for more details.

import org.jooq.meta.jaxb.*


configuration {
  generator {
    database {
      syntheticObjects {
        uniqueKeys {
          uniqueKey {

            // Optional name of the unique key, if tables matches only a single table.
            name = "UK_TABLE"

            // Optional regular expression matching all tables that have this unique key.
            tables = "SCHEMA\\.TABLE"

            // List multiple fields in the key order
            fields {
              field = "COLUMN1"
              field = "COLUMN2"
            }
          }
        }
      }
    }
  }
}

See the configuration XSD and gradle code generation for more details.

configuration {
  generator {
    database {
      syntheticObjects {
        uniqueKeys {
          uniqueKey {

            // Optional name of the unique key, if tables matches only a single table.
            name = "UK_TABLE"

            // Optional regular expression matching all tables that have this unique key.
            tables = "SCHEMA\\.TABLE"

            // List multiple fields in the key order
            fields {
              field = "COLUMN1"
              field = "COLUMN2"
            }
          }
        }
      }
    }
  }
}

See the configuration XSD and gradle code generation for more details.

generationTool {
  generator {
    database {
      syntheticObjects {
        uniqueKeys {
          uniqueKey {

            // Optional name of the unique key, if tables matches only a single table.
            name = "UK_TABLE"

            // Optional regular expression matching all tables that have this unique key.
            tables = "SCHEMA\\.TABLE"

            // List multiple fields in the key order
            fields {
              field = "COLUMN1"
              field = "COLUMN2"
            }
          }
        }
      }
    }
  }
}

See the configuration XSD and gradle code generation for more details.

As always, when regular expressions are used, they are regular expressions with default flags.