Create table with primary key using jOOQ

Question

jOOQ has CREATE TABLE syntax as stated in the documentation:

create.createTable(AUTHOR)
      .column(AUTHOR.ID, SQLDataType.INTEGER)
      .column(AUTHOR.FIRST_NAME, SQLDataType.VARCHAR.length(50))
      .column(AUTHOR_LAST_NAME, SQLDataType.VARCHAR.length(50))
      .execute();

I'm wondering how to define which column belongs to the primary key? So is there a way in jOOQ to create a CREATE TABLE statement with PRIMARY KEY information?

I'm specifically interested in a solution for SQLite, which doesn't have syntax to add the primary key afterwards, so I think in worst case I have to go to a DB specific solution?

Answer 1

This feature has been implemented for jOOQ 3.8: #4050.

create.createTable(AUTHOR)
      .column(AUTHOR.ID, SQLDataType.INTEGER)
      .column(AUTHOR.FIRST_NAME, SQLDataType.VARCHAR.length(50))
      .column(AUTHOR_LAST_NAME, SQLDataType.VARCHAR.length(50))
      .constraints(
           constraint("PK_AUTHOR").primaryKey(AUTHOR.ID)
      )
      .execute();

Since jOOQ 3.6 (#3338), you can also use the ALTER TABLE statement to add a constraint after creating the table:

create.alterTable(AUTHOR)
      .add(constraint("PK_AUTHOR").primaryKey(AUTHOR.ID))
      .execute();