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
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
Feedback
Do you have any feedback about this page? We'd love to hear it!