Available in versions: Dev (3.21) | Latest (3.20) | 3.19 | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11
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, Databricks, DuckDB, Trino
/* UNSUPPORTED */
Generated with jOOQ 3.21. Support in older jOOQ versions may differ. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!