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!