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!

The jOOQ Logo
+ show imports
+ show imports