Available in versions: Dev (3.21) | Latest (3.20) | 3.19 | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11
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.
Check constraints
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
A CHECK constraint is a simple, yet very effective means of enforcing data integrity on a row basis. Want to ensure a number is only ever positive? Use a CHECK constraint(or even a DOMAIN that contains a CHECK constraint).
// Create a new table with columns and unnamed constraints
create.createTable("table")
.column("column1", INTEGER)
.check(field(name("column1"), INTEGER).gt(0))
.execute();
// Equivalent to the above
create.createTable("table")
.column("column1", INTEGER)
.constraints(
check(field(name("column1"), INTEGER).gt(0))
)
.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("chk").check(field(name("column1"), INTEGER).gt(0))
)
.execute();
Just like the previous constraints, this one can be used by the optimiser to remove some redundant predicates, see e.g. this blog post.
Dialect support
This example using jOOQ:
createTable("table")
.column("column1", INTEGER)
.constraints(
constraint("chk").check(field(name("column1"), INTEGER).gt(0))
)
Translates to the following dialect specific expressions:
Access, DB2, Firebird, Hana, Teradata
CREATE TABLE table ( column1 integer, CONSTRAINT chk CHECK (column1 > 0) )
ASE, Sybase
CREATE TABLE table ( column1 int NULL, CONSTRAINT chk CHECK (column1 > 0) )
Aurora Postgres, Derby, DuckDB, H2, HSQLDB, MariaDB, MySQL, Postgres, SQLServer, SQLite, Vertica, YugabyteDB
CREATE TABLE table ( column1 int, CONSTRAINT chk CHECK (column1 > 0) )
ClickHouse
CREATE TABLE table ( column1 Nullable(integer), CONSTRAINT chk CHECK (column1 > 0) ) ENGINE Log()
CockroachDB
CREATE TABLE table ( column1 int4, CONSTRAINT chk CHECK (column1 > 0) )
Informix
CREATE TABLE table ( column1 integer, CHECK (column1 > 0) CONSTRAINT chk )
Oracle
CREATE TABLE table ( column1 number(10), CONSTRAINT chk CHECK (column1 > 0) )
Aurora MySQL, BigQuery, Databricks, Exasol, MemSQL, Redshift, SQLDataWarehouse, Snowflake, Trino
/* UNSUPPORTED */
Generated with jOOQ 3.21. Support in older jOOQ versions may differ. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!