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

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

Feedback

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

The jOOQ Logo