Synthetic primary keys
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
jOOQ's code generator recognises primary 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 primary key. If a composite synthetic primary key is desired, the regular expression should match all columns of that table that are part of the primary key. For example, a composite synthetic primary key consists of (COLUMN1, COLUMN2) in table SCHEMA.TABLE:
<configuration>
<generator>
<database>
<syntheticObjects>
<primaryKeys>
<primaryKey>
<!-- Optional name of the primary key, if tables matches only a single table. -->
<name>PK_TABLE</name>
<!-- Optional regular expression matching all tables that have this primary key. -->
<tables>SCHEMA\.TABLE</tables>
<!-- List multiple fields in the key order -->
<fields>
<field>COLUMN1</field>
<field>COLUMN2</field>
</fields>
<!-- Alternatively, instead of listing columns above, promote a unique key to a primary key, by name. -->
<key>UK_TABLE</key>
</primaryKey>
</primaryKeys>
</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()
.withPrimaryKeys(
new SyntheticPrimaryKeyType()
// Optional name of the primary key, if tables matches only a single table.
.withName("PK_TABLE")
// Optional regular expression matching all tables that have this primary key.
.withTables("SCHEMA\\.TABLE")
// List multiple fields in the key order
.withFields(
"COLUMN1",
"COLUMN2"
)
// Alternatively, instead of listing columns above, promote a unique key to a primary key, by name.
.withKey("UK_TABLE")
)
)
)
)
See the configuration XSD and programmatic code generation for more details.
// The jOOQ-codegen-gradle plugin has been introduced in version 3.19 only.
// The jOOQ-codegen-gradle plugin has been introduced in version 3.19 only.
generationTool {
generator {
database {
syntheticObjects {
primaryKeys {
primaryKey {
// Optional name of the primary key, if tables matches only a single table.
name = "PK_TABLE"
// Optional regular expression matching all tables that have this primary key.
tables = "SCHEMA\\.TABLE"
// List multiple fields in the key order
fields {
field = "COLUMN1"
field = "COLUMN2"
}
// Alternatively, instead of listing columns above, promote a unique key to a primary key, by name.
key = "UK_TABLE"
}
}
}
}
}
}
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.
If the regular expression matches column in a table that already has an existing primary key, that existing primary key will be replaced by the synthetic one. It will still be reported as a unique key, though.
Feedback
Do you have any feedback about this page? We'd love to hear it!