Synthetic foreign keys
Applies to ❌ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
jOOQ's code generator recognises foreign 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 foreign key. If a composite synthetic foreign key is desired, the regular expression should match all columns of that table that are part of the foreign key. For example, a composite synthetic foreign key consists of (COLUMN1, COLUMN2) in table SCHEMA.TABLE:
<configuration>
<generator>
<database>
<syntheticObjects>
<foreignKeys>
<foreignKey>
<!-- Optional name of the foreign key, if tables matches only a single table. -->
<!-- This is useful to disambiguate navigational methods and implicit join methods! -->
<name>FK_TABLE</name>
<!-- Optional regular expression matching all tables that have this foreign key. -->
<tables>SCHEMA\.TABLE</tables>
<!-- List multiple fields in the key order -->
<fields>
<field>COLUMN1</field>
<field>COLUMN2</field>
</fields>
<!-- Specify the table that is being referenced by this foreign key -->
<referencedTable>SCHEMA\.OTHER_TABLE</referencedTable>
<!-- Optional: The primary or unique key columns that are being referenced -->
<referencedFields>
<field>COLUMN1</field>
<field>COLUMN2</field>
</referencedFields>
<!-- Optional: reference a primary or unique key by name -->
<!-- If the referenced fields or key are not specified, the referencedTable's primary key is used -->
<referencedKey>UK_TABLE</referencedKey>
</foreignKey>
</foreignKeys>
</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()
.withForeignKeys(
new SyntheticForeignKeyType()
// Optional name of the foreign key, if tables matches only a single table.
.withName("FK_TABLE")
// Optional regular expression matching all tables that have this foreign key.
.withTables("SCHEMA\\.TABLE")
// List multiple fields in the key order
.withFields(
"COLUMN1",
"COLUMN2"
)
// Specify the table that is being referenced by this foreign key
.withReferencedTable("SCHEMA\\.OTHER_TABLE")
// Optional: The primary or unique key columns that are being referenced
.withReferencedFields(new ()
.withField("COLUMN1")
.withField("COLUMN2")
)
// Optional: reference a primary or unique key by name
.withReferencedKey("UK_TABLE")
)
)
)
)
See the configuration XSD and programmatic code generation for more details.
import org.jooq.meta.jaxb.*
configuration {
generator {
database {
syntheticObjects {
foreignKeys {
foreignKey {
// Optional name of the foreign key, if tables matches only a single table.
name = "FK_TABLE"
// Optional regular expression matching all tables that have this foreign key.
tables = "SCHEMA\\.TABLE"
// List multiple fields in the key order
fields {
field = "COLUMN1"
field = "COLUMN2"
}
// Specify the table that is being referenced by this foreign key
referencedTable = "SCHEMA\\.OTHER_TABLE"
// Optional: The primary or unique key columns that are being referenced
referencedFields {
field = "COLUMN1"
field = "COLUMN2"
}
// Optional: reference a primary or unique key by name
referencedKey = "UK_TABLE"
}
}
}
}
}
}
See the configuration XSD and gradle code generation for more details.
configuration {
generator {
database {
syntheticObjects {
foreignKeys {
foreignKey {
// Optional name of the foreign key, if tables matches only a single table.
name = "FK_TABLE"
// Optional regular expression matching all tables that have this foreign key.
tables = "SCHEMA\\.TABLE"
// List multiple fields in the key order
fields {
field = "COLUMN1"
field = "COLUMN2"
}
// Specify the table that is being referenced by this foreign key
referencedTable = "SCHEMA\\.OTHER_TABLE"
// Optional: The primary or unique key columns that are being referenced
referencedFields {
field = "COLUMN1"
field = "COLUMN2"
}
// Optional: reference a primary or unique key by name
referencedKey = "UK_TABLE"
}
}
}
}
}
}
See the configuration XSD and gradle code generation for more details.
generationTool {
generator {
database {
syntheticObjects {
foreignKeys {
foreignKey {
// Optional name of the foreign key, if tables matches only a single table.
name = "FK_TABLE"
// Optional regular expression matching all tables that have this foreign key.
tables = "SCHEMA\\.TABLE"
// List multiple fields in the key order
fields {
field = "COLUMN1"
field = "COLUMN2"
}
// Specify the table that is being referenced by this foreign key
referencedTable = "SCHEMA\\.OTHER_TABLE"
// Optional: The primary or unique key columns that are being referenced
referencedFields {
field = "COLUMN1"
field = "COLUMN2"
}
// Optional: reference a primary or unique key by name
referencedKey = "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.
Feedback
Do you have any feedback about this page? We'd love to hear it!