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
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)
.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, SYBASE CREATE TABLE table ( column1 int NULL, CONSTRAINT pk PRIMARY KEY (column1) ) -- AURORA_MYSQL, AURORA_POSTGRES, DERBY, DUCKDB, EXASOL, H2, HSQLDB, POSTGRES, REDSHIFT, YUGABYTEDB CREATE TABLE table ( column1 int, CONSTRAINT pk PRIMARY KEY (column1) ) -- BIGQUERY CREATE TABLE table ( column1 int64 ) -- 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 ) -- MARIADB, MEMSQL, MYSQL, SQLSERVER CREATE TABLE table ( column1 int NOT NULL, CONSTRAINT pk PRIMARY KEY (column1) ) -- 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 ) -- SQLITE CREATE TABLE "table" ( column1 int, CONSTRAINT pk PRIMARY KEY (column1) ) -- TRINO CREATE TABLE table ( column1 int ) -- VERTICA CREATE TABLE table ( column1 int, dummy int, CONSTRAINT pk PRIMARY KEY (column1) )
(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!