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
Foreign keys
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
A foreign key is a tool that helps further normalise your database by guaranteeing that a referenced value exists in a parent table. In our sample database, it enforces the integrity of the BOOK.AUTHOR_ID reference. Besides integrity, it can be a very useful tool for optimising more sophisticated execution plans, e.g. to support JOIN elimination. In jOOQ, create foreign keys like this:
// Create a new table with columns and unnamed constraints
create.createTable("table")
.column("column1", INTEGER)
.constraints(
foreignKey("column1").references("other_table", "other_column1")
)
.execute();
// Create a new table with columns and named constraints (recommended if you want to alter the constraint)
create.createTable("table")
.column("column1", INTEGER)
.constraints(
constraint("fk").foreignKey("column1").references("other_table", "other_column1")
)
.execute();
jOOQ's code generator will pick up foreign keys for a variety of purposes, including navigational methods, the ON KEY joins.
Dialect support
This example using jOOQ:
createTable("table")
.column("column1", INTEGER)
.constraints(
constraint("fk").foreignKey("column1").references("other_table", "other_column1")
)
Translates to the following dialect specific expressions:
-- ACCESS, DB2, FIREBIRD, HANA, TERADATA CREATE TABLE table ( column1 integer, CONSTRAINT fk FOREIGN KEY (column1) REFERENCES other_table (other_column1) ) -- ASE, SYBASE CREATE TABLE table ( column1 int NULL, CONSTRAINT fk FOREIGN KEY (column1) REFERENCES other_table (other_column1) ) -- AURORA_MYSQL, AURORA_POSTGRES, DERBY, DUCKDB, EXASOL, H2, HSQLDB, MARIADB, MEMSQL, MYSQL, POSTGRES, REDSHIFT, -- SQLDATAWAREHOUSE, SQLSERVER, VERTICA, YUGABYTEDB CREATE TABLE table ( column1 int, CONSTRAINT fk FOREIGN KEY (column1) REFERENCES other_table (other_column1) ) -- BIGQUERY CREATE TABLE table ( column1 int64 ) -- COCKROACHDB CREATE TABLE table ( column1 int4, CONSTRAINT fk FOREIGN KEY (column1) REFERENCES other_table (other_column1) ) -- INFORMIX CREATE TABLE table ( column1 integer, FOREIGN KEY (column1) REFERENCES other_table (other_column1) CONSTRAINT fk ) -- ORACLE, SNOWFLAKE CREATE TABLE table ( column1 number(10), CONSTRAINT fk FOREIGN KEY (column1) REFERENCES other_table (other_column1) ) -- SQLITE CREATE TABLE "table" ( column1 int, CONSTRAINT fk FOREIGN KEY (column1) REFERENCES other_table (other_column1) ) -- TRINO CREATE TABLE table ( column1 int )
(These are currently generated with jOOQ 3.19, see #10141), or translate your own on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!