The jOOQ User Manual SQL building SQL Statements (DDL) The ALTER statement ALTER TABLE

previous : next

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 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.

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();

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

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

DROP

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

// Drop a single column
create.alterTable("table").drop("column").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();

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 User Manual SQL building SQL Statements (DDL) The ALTER statement ALTER TABLE

previous : next