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

This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.

Identities

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

An IDENTITY is a special type of DEFAULT on a column, which is computed only on INSERT, and should usually not be replaced by user content. It computes a new value for a surrogate key. Most dialects default to using some system sequence based IDENTITY, though a UUID or some other unique value might work as well.

In jOOQ, it is currently only possible to specify whether a column is an IDENTITY at all, not to influence the value generation algorithm.

// Create a new table with a column with a default expression
create.createTable("table")
      .column("column1", INTEGER.identity(true))
      .execute();

Whether an IDENTITY also needs to be explicitly NOT NULL or a PRIMARY KEY is vendor specific. Ideally, both of these properties are set as well on identities.

Dialect support

This example using jOOQ:

createTable("table")
      .column("column1", INTEGER.identity(true))

Translates to the following dialect specific expressions:

Access

CREATE TABLE table (
  column1 AUTOINCREMENT NOT NULL
)

ASE, Exasol

CREATE TABLE table (
  column1 int IDENTITY NOT NULL
)

Aurora MySQL, MariaDB, MemSQL, MySQL

CREATE TABLE table (
  column1 int NOT NULL AUTO_INCREMENT
)

Aurora Postgres

CREATE TABLE table (
  column1 SERIAL4 NOT NULL
)

CockroachDB

CREATE TABLE table (
  column1 integer DEFAULT (unique_rowid() % 2 ^ 31) NOT NULL
)

DB2, Firebird

CREATE TABLE table (
  column1 integer GENERATED BY DEFAULT AS IDENTITY NOT NULL
)

Derby, Postgres, YugabyteDB

CREATE TABLE table (
  column1 int GENERATED BY DEFAULT AS IDENTITY NOT NULL
)

H2

CREATE TABLE table (
  column1 int NOT NULL GENERATED BY DEFAULT AS IDENTITY
)

Hana, Teradata

CREATE TABLE table (
  column1 integer NOT NULL GENERATED BY DEFAULT AS IDENTITY
)

HSQLDB

CREATE TABLE table (
  column1 int GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL
)

Informix

CREATE TABLE table (
  column1 SERIAL NOT NULL
)

Oracle

CREATE TABLE table (
  column1 number(10) GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL
)

Redshift, SQLDataWarehouse, SQLServer

CREATE TABLE table (
  column1 int IDENTITY(1, 1) NOT NULL
)

Snowflake

CREATE TABLE table (
  column1 number(10) IDENTITY NOT NULL
)

SQLite

CREATE TABLE table (
  column1 integer PRIMARY KEY AUTOINCREMENT NOT NULL
)

Sybase

CREATE TABLE table (
  column1 int NOT NULL IDENTITY
)

Vertica

CREATE TABLE table (
  column1 IDENTITY(1, 1) NOT NULL
)

BigQuery, ClickHouse, DuckDB, Trino

/* UNSUPPORTED */
Generated with jOOQ 3.20. Translate your own SQL 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