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

previous : next

Constraint deferrability

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

Some RDBMS support deferring the enforcement of constraints. 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

This is generally a safer alternative to turning off enforcement entirely as it affects only local transaction scope. Some RDBMS even allow for overriding the constraint's property on a per-transaction basis using the SET CONSTRAINTS statement.

jOOQ supports specifying whether a constraint should be immediately enforced or not using the DEFERRABLE and DEFERRED keywords.

Dialect support

This example using jOOQ:

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

Translates to the following dialect specific expressions:

Hana

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

Oracle

CREATE TABLE table (
  column1 number(10),
  CONSTRAINT chk CHECK (column1 > 0) DEFERRABLE INITIALLY DEFERRED
)

Postgres, SQLite, YugabyteDB

CREATE TABLE table (
  column1 int,
  CONSTRAINT chk CHECK (column1 > 0) DEFERRABLE INITIALLY DEFERRED
)

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

/* UNSUPPORTED */

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

previous : next