The jOOQ User Manual SQL building SQL Statements (DDL) The CREATE statement CREATE TABLE Constraint enforcement

previous : next

Constraint enforcement

Supported by ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

Some RDBMS support disabling constraints, or removing constraint enforcement, respectively. This can be useful for various purposes:

• Simplify data migrations
• Speed up querying while maintaining descriptive meta data (e.g. in data warehouses)
• Tooling interactions

jOOQ supports specifying whether a constraint should be enforced or not using the ENFORCED and NOT ENFORCED keywords.

Dialect support

This example using jOOQ:

createTable("table")
      .column("column1", INTEGER)
      .constraints(
          constraint("chk").check(field(name("column1"), INTEGER).gt(0)).notEnforced()
      )

Translates to the following dialect specific expressions:

DB2, Hana

CREATE TABLE table (
  column1 integer,
  CONSTRAINT chk CHECK (column1 > 0) NOT ENFORCED
)

Informix

CREATE TABLE table (
  column1 integer,
  CHECK (column1 > 0) CONSTRAINT chk DISABLED
)

MySQL, Postgres

CREATE TABLE table (
  column1 int,
  CONSTRAINT chk CHECK (column1 > 0) NOT ENFORCED
)

Oracle

CREATE TABLE table (
  column1 number(10),
  CONSTRAINT chk CHECK (column1 > 0) DISABLE
)

Spanner

CREATE TABLE table (
  column1 int64,
  CONSTRAINT chk CHECK (column1 > 0) NOT ENFORCED
)

Vertica

CREATE TABLE table (
  column1 int,
  CONSTRAINT chk CHECK (column1 > 0) DISABLED
)

ASE, Access, Aurora MySQL, Aurora Postgres, BigQuery, ClickHouse, CockroachDB, Databricks, DuckDB, Exasol, Firebird, H2, HSQLDB, MariaDB, MemSQL, Redshift, SQLDataWarehouse, SQLServer, SQLite, Snowflake, Sybase, Teradata, Trino, YugabyteDB

/* UNSUPPORTED */

The jOOQ User Manual SQL building SQL Statements (DDL) The CREATE statement CREATE TABLE Constraint enforcement

previous : next