Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10

Nullability

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

Nullability is a property of a data type, and as such can be attached to the data type using various methods. The default nullability is RDBMS specific, so if you want to be vendor agnostic about nullability in your DDL, better always state it explicitly, for example:

// Specify nullability on columns
create.createTable("table")
      .column("vendor_specific_default", INTEGER)
      .column("explicit_nullable", INTEGER.nullable(false))
      .column("explicit_not_nullable", INTEGER.nullable(true))
      .execute();

Dialect support

This example using jOOQ:

createTable("table")
      .column("vendor_specific_default", INTEGER)
      .column("explicit_nullable", INTEGER.nullable(false))
      .column("explicit_not_nullable", INTEGER.nullable(true))

Translates to the following dialect specific expressions:

Access, DB2, Hana, Informix, Teradata

CREATE TABLE table (
  vendor_specific_default integer,
  explicit_nullable integer NOT NULL,
  explicit_not_nullable integer NULL
)

ASE, Sybase

CREATE TABLE table (
  vendor_specific_default int NULL,
  explicit_nullable int NOT NULL,
  explicit_not_nullable int NULL
)

Aurora MySQL, Aurora Postgres, DuckDB, Exasol, MariaDB, MemSQL, MySQL, Postgres, Redshift, SQLDataWarehouse, SQLServer, Vertica, YugabyteDB

CREATE TABLE table (
  vendor_specific_default int,
  explicit_nullable int NOT NULL,
  explicit_not_nullable int NULL
)

BigQuery

CREATE TABLE table (
  vendor_specific_default int64,
  explicit_nullable int64 NOT NULL,
  explicit_not_nullable int64
)

ClickHouse

CREATE TABLE table (
  vendor_specific_default Nullable(integer),
  explicit_nullable integer,
  explicit_not_nullable Nullable(integer)
)
ENGINE Log()

CockroachDB

CREATE TABLE table (
  vendor_specific_default int4,
  explicit_nullable int4 NOT NULL,
  explicit_not_nullable int4 NULL
)

Derby, H2, HSQLDB

CREATE TABLE table (
  vendor_specific_default int,
  explicit_nullable int NOT NULL,
  explicit_not_nullable int
)

Firebird

CREATE TABLE table (
  vendor_specific_default integer,
  explicit_nullable integer NOT NULL,
  explicit_not_nullable integer
)

Oracle, Snowflake

CREATE TABLE table (
  vendor_specific_default number(10),
  explicit_nullable number(10) NOT NULL,
  explicit_not_nullable number(10) NULL
)

SQLite

CREATE TABLE "table" (
  vendor_specific_default int,
  explicit_nullable int NOT NULL,
  explicit_not_nullable int NULL
)

Trino

CREATE TABLE table (
  vendor_specific_default int,
  explicit_nullable int,
  explicit_not_nullable int
)

(These are currently generated with jOOQ 3.20, see #10141), or translate your own on our website

References to this page

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo