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
Unique constraints
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
A candidate key that is not ideal for a Primary key should still be declared UNIQUE to enforce uniqueness, as well as for query performance reasons. In jOOQ, this can be done with the following approaches:
// Create a new table with columns and unnamed constraints
create.createTable("table")
.column("column1", INTEGER)
.column("column2", INTEGER)
.column("column3", INTEGER)
.constraints(
unique("column1"),
unique("column2", "column3")
)
.execute();
// Create a new table with columns and named constraints (recommended if you want to alter the constraint)
create.createTable("table")
.column("column1", INTEGER)
.column("column2", INTEGER)
.column("column3", INTEGER)
.constraints(
constraint("uk1").unique("column1"),
constraint("uk2").unique("column2", "column3")
)
.execute();
Dialect support
This example using jOOQ:
createTable("table")
.column("column1", INTEGER)
.constraints(
constraint("uk").unique("column1")
)
Translates to the following dialect specific expressions:
-- ACCESS, DB2, FIREBIRD, HANA, TERADATA CREATE TABLE table ( column1 integer, CONSTRAINT uk UNIQUE (column1) ) -- ASE, SYBASE CREATE TABLE table ( column1 int NULL, CONSTRAINT uk UNIQUE (column1) ) -- AURORA_MYSQL, AURORA_POSTGRES, DERBY, DUCKDB, EXASOL, H2, HSQLDB, MARIADB, MEMSQL, MYSQL, POSTGRES, REDSHIFT, -- SQLSERVER, VERTICA, YUGABYTEDB CREATE TABLE table ( column1 int, CONSTRAINT uk UNIQUE (column1) ) -- BIGQUERY CREATE TABLE table ( column1 int64 ) -- COCKROACHDB CREATE TABLE table ( column1 int4, CONSTRAINT uk UNIQUE (column1) ) -- INFORMIX CREATE TABLE table ( column1 integer, UNIQUE (column1) CONSTRAINT uk ) -- ORACLE, SNOWFLAKE CREATE TABLE table ( column1 number(10), CONSTRAINT uk UNIQUE (column1) ) -- SQLDATAWAREHOUSE CREATE TABLE table ( column1 int, CONSTRAINT uk UNIQUE (column1) NOT ENFORCED ) -- SQLITE CREATE TABLE "table" ( column1 int, CONSTRAINT uk UNIQUE (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!