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
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, 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!