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

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.

Primary key

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

In a normalised database, all tables should have a PRIMARY KEY. In jOOQ, numerous features are enabled by tables that have one, including for example UpdatableRecords. To create a table with a primary key, write any of these:

// Create a new table with columns and unnamed constraints
create.createTable("table")
      .column("column1", INTEGER)
      .primaryKey("column1")
      .execute();

// Equivalent to the above
create.createTable("table")
      .column("column1", INTEGER)
      .constraints(
          primaryKey("column1")
      )
      .execute();

// Create a new table with columns and named constraints (recommended if you want to alter the constraint)
create.createTable("table")
      .column("column1", INTEGER)
      .constraints(
          constraint("pk").primaryKey("column1")
      )
      .execute();

Dialect support

This example using jOOQ:

createTable("table")
      .column("column1", INTEGER)
      .constraints(
          constraint("pk").primaryKey("column1")
      )

Translates to the following dialect specific expressions:

Access, Firebird, Hana

CREATE TABLE table (
  column1 integer,
  CONSTRAINT pk PRIMARY KEY (column1)
)

ASE, MariaDB, MemSQL, MySQL, SQLServer

CREATE TABLE table (
  column1 int NOT NULL,
  CONSTRAINT pk PRIMARY KEY (column1)
)

Aurora MySQL, Aurora Postgres, Derby, DuckDB, Exasol, H2, HSQLDB, Postgres, Redshift, SQLite, YugabyteDB

CREATE TABLE table (
  column1 int,
  CONSTRAINT pk PRIMARY KEY (column1)
)

BigQuery

CREATE TABLE table (
  column1 int64,
  PRIMARY KEY (column1) NOT ENFORCED
)

ClickHouse

CREATE TABLE table (
  column1 integer,
  PRIMARY KEY (column1)
)
ENGINE MergeTree()

CockroachDB

CREATE TABLE table (
  column1 int4,
  CONSTRAINT pk PRIMARY KEY (column1)
)

DB2, Teradata

CREATE TABLE table (
  column1 integer NOT NULL,
  CONSTRAINT pk PRIMARY KEY (column1)
)

Informix

CREATE TABLE table (
  column1 integer,
  PRIMARY KEY (column1) CONSTRAINT pk
)

Oracle, Snowflake

CREATE TABLE table (
  column1 number(10),
  CONSTRAINT pk PRIMARY KEY (column1)
)

SQLDataWarehouse

CREATE TABLE table (
  column1 int,
  CONSTRAINT pk PRIMARY KEY NONCLUSTERED (column1) NOT ENFORCED
)

Sybase

CREATE TABLE table (
  column1 int NULL,
  CONSTRAINT pk PRIMARY KEY (column1)
)

Trino

CREATE TABLE table (
  column1 int
)

Vertica

CREATE TABLE table (
  column1 int,
  dummy int,
  CONSTRAINT pk PRIMARY KEY (column1)
)
Generated with jOOQ 3.20. Translate your own SQL on our website

References to this page

Feedback

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

The jOOQ Logo