New versions: Dev (3.14) | Latest (3.13) | 3.12 | 3.11 | 3.10 | 3.9 | 3.8 | Old versions: 3.7 | 3.6 | 3.5 | 3.4

ALTER TABLE

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

The ALTER TABLE statement is certainly the most powerful among DDL statements, as tables are the most important object type in a database catalog. The following types of statements are supported when altering a table:

ADD

In most dialects, tables can contain two types of objects:

  • Columns
  • Constraints

These types of objects can be added to a table using the following API:

// Adding a single column to a table
create.alterTable("table").add("column", SQLDataType.INTEGER).execute();

// Adding several columns to a table in one go
create.alterTable("table").add("column1", SQLDataType.INTEGER).add("column2", SQLDataType.INTEGER).execute();

// Adding an unnamed constraint to a table
create.alterTable("table").add(primaryKey("id")).execute();
create.alterTable("table").add(unique("user_name")).execute();
create.alterTable("table").add(foreignKey("author_id").references("author")).execute();
create.alterTable("table").add(check(length(field(name("user_name"), SQLDataType.VARCHAR)).gt(5))).execute();

// Adding a named constraint to a table
create.alterTable("table").add(constraint("pk").primaryKey("id")).execute();
create.alterTable("table").add(constraint("uk").unique("user_name")).execute();
create.alterTable("table").add(constraint("fk").foreignKey("author_id").references("author")).execute();
create.alterTable("table").add(constraint("ck").check(length(field(name("user_name"), SQLDataType.VARCHAR)).gt(5))).execute();

There exists alternative API representing optional keywords, such as e.g. addColumn(), which have been omitted from the examples.

It is possible to specify the column ordering when adding new columns, where this is supported:

// Adding a single column and specify its position
create.alterTable("table").add("column", SQLDataType.INTEGER).after("other_column").execute();
create.alterTable("table").add("column", SQLDataType.INTEGER).before("other_column").execute();
create.alterTable("table").add("column", SQLDataType.INTEGER).first().execute();

Note that some dialects also consider indexes to be a part of a table, but jOOQ does not yet support ALTER TABLE subclauses modifying indexes. Consider CREATE INDEX, ALTER INDEX, or DROP INDEX, instead.

ALTER

Both of the above objects can be altered in a table using the following API:

// Specify a new default value for a column
create.alterTable("table").alter("column").default_(1).execute();
create.alterTable("table").alter("column").dropDefault().execute();

// Specify the not null constraint on a column
create.alterTable("table").alter("column").setNotNull().execute();
create.alterTable("table").alter("column").dropNotNull().execute();

// Set a new data type on the column
create.alterTable("table").alter("column").set(SQLDataType.VARCHAR(50)).execute();

// Set the enforced flag on a constraint
create.alterTable("table").alterConstraint("uk").enforced().execute();
create.alterTable("table").alterConstraint("uk").notEnforced().execute();

There exists alternative API representing optional keywords, such as e.g. alterColumn(), which have been omitted from the examples.

COMMENT

For convenience, jOOQ supports MySQL's COMMENT syntax also on ALTER TABLE, which corresponds to the more standard COMMENT ON TABLE statement

// Specify a new comment on a table
create.alterTable("table").comment("a comment describing the table").execute();

DROP

Both columns and constraints can also be dropped from tables using this API:

// Drop a single column
create.alterTable("table").drop("column").execute();

// Drop several columns in one go
create.alterTable("table").drop("column1", "column2").execute();

// Add CASCADE or RESTRICT clauses when dropping columns (or constraints)
create.alterTable("table").drop("column").cascade().execute();
create.alterTable("table").drop("column").restrict().execute();

// Drop a constraint
create.alterTable("table").dropConstraint("uk").execute();

// Drop specific types of constraints (e.g. if the above syntax is not supported by the dialect)
create.alterTable("table").dropPrimaryKey().execute();
create.alterTable("table").dropUnique("uk").execute();
create.alterTable("table").dropForeignKey("fk").execute();

RENAME

Like most object types, tables, columns, and constraints can be renamed:

// Rename a table
create.alterTable("old_table").renameTo("new_table").execute();

// Rename a column
create.alterTable("table").renameColumn("old_column").to("new_column").execute();

// Rename a constraint
create.alterTable("table").renameConstraint("old_constraint").to("new_constraint").execute();

// Rename a index (as a convenience for the ALTER INDEX statement)
create.alterTable("table").renameIndex("old_index").to("new_index").execute();
The jOOQ Logo