Available in versions: Dev (3.19) | Latest (3.18) | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9
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, DUCKDB, TRINO /* UNSUPPORTED */
(These are currently generated with jOOQ 3.19, see #10141), or translate your own on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!