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
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.
// Create a new table with columns and unnamed constraints
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, H2, HSQLDB, MARIADB, MYSQL, POSTGRES, SQLSERVER, VERTICA, YUGABYTEDB CREATE TABLE table ( column1 int, CONSTRAINT chk CHECK (column1 > 0) ) -- 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) ) -- SQLITE CREATE TABLE "table" ( column1 int, CONSTRAINT chk CHECK (column1 > 0) ) -- AURORA_MYSQL, BIGQUERY, DUCKDB, EXASOL, MEMSQL, REDSHIFT, SNOWFLAKE, SQLDATAWAREHOUSE, TRINO /* UNSUPPORTED */
(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!