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

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
/* UNSUPPORTED */

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

Feedback

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

The jOOQ Logo