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
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.
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", INTEGER).execute();
// Adding several columns to a table in one go
create.alterTable("table").add(field(name("column1"), INTEGER), field(name("column2"), 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"), 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"), 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", INTEGER).after("other_column").execute();
create.alterTable("table").add("column", INTEGER).before("other_column").execute();
create.alterTable("table").add("column", 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(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();
Feedback
Do you have any feedback about this page? We'd love to hear it!