previous : next

Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10

SQL Statements (DDL)

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

The Data Definition Language (DDL) is used to CREATE, ALTER, and DROP various object types in the database catalog. jOOQ supports an increasing number of these operations natively, and also adds synthetic operation support for convenience.

While many DDL statements are supported natively, and have a 1:1 correspondence to the jOOQ API's representation, dialects differ in many subtle ways when it comes to DDL statement support. These differences may include:

  • Different keywords to mean the same thing. For example, the keywords ALTER, CHANGE, or MODIFY may be used when altering columns or other attributes in a table.
  • Different statements instead of subclauses. For example, some dialects may choose to support RENAME [object type] .. TO .. statements instead of making the rename operation a subclause of ALTER [object type] .. RENAME TO ..
  • Some syntax may not be supported, or not be supported consistently, such as the various IF EXISTS and IF NOT EXISTS clauses. Emulations are possible using the dialect's procedural language

Because of these many differences, the jOOQ manual will not list each individual native SQL representation of each jOOQ API call. Also, some optional clauses may exist, such as the IF EXISTS or OR REPLACE clauses, which can easily be discovered from the API. The manual will omit documenting these clauses in every example.

Commercial support for emulations

A lot of DDL queries come with syntax that requires emulation using anonymous blocks. While basic anonymous blocks are supported in the jOOQ Open Source Edition as well, more sophisticated blocks and other procedural logic is a commercial only feature.

Table of contents

3.4.1.
The ALTER statement
3.4.1.1.
ALTER DATABASE
3.4.1.1.1.
ALTER DATABASE .. RENAME
3.4.1.1.2.
ALTER DATABASE IF EXISTS
3.4.1.2.
ALTER DOMAIN
3.4.1.2.1.
ALTER DOMAIN .. RENAME
3.4.1.2.2.
ALTER DOMAIN .. SET DEFAULT
3.4.1.2.3.
ALTER DOMAIN .. DROP DEFAULT
3.4.1.2.4.
ALTER DOMAIN .. SET NOT NULL
3.4.1.2.5.
ALTER DOMAIN .. DROP NOT NULL
3.4.1.2.6.
ALTER DOMAIN .. ADD CONSTRAINT
3.4.1.2.7.
ALTER DOMAIN .. RENAME CONSTRAINT
3.4.1.2.8.
ALTER DOMAIN .. RENAME CONSTRAINT IF EXISTS
3.4.1.2.9.
ALTER DOMAIN .. DROP CONSTRAINT
3.4.1.2.10.
ALTER DOMAIN .. DROP CONSTRAINT IF EXISTS
3.4.1.2.11.
ALTER DOMAIN IF EXISTS
3.4.1.3.
ALTER INDEX
3.4.1.3.1.
ALTER INDEX .. RENAME
3.4.1.3.2.
ALTER INDEX IF EXISTS
3.4.1.4.
ALTER SCHEMA
3.4.1.4.1.
ALTER SCHEMA .. RENAME
3.4.1.4.2.
ALTER SCHEMA IF EXISTS
3.4.1.5.
ALTER SEQUENCE
3.4.1.5.1.
ALTER SEQUENCE .. RENAME
3.4.1.5.2.
ALTER SEQUENCE .. CACHE
3.4.1.5.3.
ALTER SEQUENCE .. CYCLE
3.4.1.5.4.
ALTER SEQUENCE .. MINVALUE
3.4.1.5.5.
ALTER SEQUENCE .. t reaches its MAXVALUE
3.4.1.5.6.
ALTER SEQUENCE .. INCREMENT BY
3.4.1.5.7.
ALTER SEQUENCE .. START WITH
3.4.1.5.8.
ALTER SEQUENCE .. RESTART
3.4.1.5.9.
ALTER SEQUENCE IF EXISTS
3.4.1.6.
ALTER TABLE
3.4.1.6.1.
ALTER TABLE .. ADD COLUMN
3.4.1.6.2.
ALTER TABLE .. ADD COLUMN .. FIRST, BEFORE, AFTER
3.4.1.6.3.
ALTER TABLE .. ADD COLUMNS
3.4.1.6.4.
ALTER TABLE .. ADD COLUMN IF NOT EXISTS
3.4.1.6.5.
ALTER TABLE .. ADD PRIMARY KEY
3.4.1.6.6.
ALTER TABLE .. ADD UNIQUE
3.4.1.6.7.
ALTER TABLE .. ADD FOREIGN KEY
3.4.1.6.8.
ALTER TABLE .. ADD CHECK
3.4.1.6.9.
ALTER TABLE .. RENAME
3.4.1.6.10.
ALTER TABLE .. COMMENT
3.4.1.6.11.
ALTER TABLE .. ALTER COLUMN .. SET DEFAULT
3.4.1.6.12.
ALTER TABLE .. ALTER COLUMN .. DROP DEFAULT
3.4.1.6.13.
ALTER TABLE .. ALTER COLUMN .. SET NOT NULL
3.4.1.6.14.
ALTER TABLE .. ALTER COLUMN .. DROP NOT NULL
3.4.1.6.15.
ALTER TABLE .. ALTER COLUMN .. SET TYPE
3.4.1.6.16.
ALTER TABLE .. ALTER CONSTRAINT .. ENFORCED
3.4.1.6.17.
ALTER TABLE .. ALTER CONSTRAINT .. NOT ENFORCED
3.4.1.6.18.
ALTER TABLE .. RENAME COLUMN
3.4.1.6.19.
ALTER TABLE .. RENAME CONSTRAINT
3.4.1.6.20.
ALTER TABLE .. RENAME INDEX
3.4.1.6.21.
ALTER TABLE .. DROP COLUMN
3.4.1.6.22.
ALTER TABLE .. DROP COLUMN RESTRICT
3.4.1.6.23.
ALTER TABLE .. DROP COLUMN CASCADE
3.4.1.6.24.
ALTER TABLE .. DROP COLUMNS
3.4.1.6.25.
ALTER TABLE .. ADD COLUMN IF NOT EXISTS
3.4.1.6.26.
ALTER TABLE .. DROP CONSTRAINT
3.4.1.6.27.
ALTER TABLE .. DROP PRIMARY KEY
3.4.1.6.28.
ALTER TABLE .. DROP UNIQUE
3.4.1.6.29.
ALTER TABLE .. DROP FOREIGN KEY
3.4.1.6.30.
ALTER TABLE .. DROP CONSTRAINT IF EXISTS
3.4.1.6.31.
ALTER TABLE IF EXISTS
3.4.1.7.
ALTER TYPE
3.4.1.7.1.
ALTER TYPE .. RENAME
3.4.1.7.2.
ALTER TYPE .. for enum alterations
3.4.1.8.
ALTER VIEW
3.4.1.8.1.
ALTER VIEW .. COMMENT
3.4.1.8.2.
ALTER VIEW .. RENAME
3.4.1.8.3.
ALTER VIEW IF EXISTS
3.4.2.
The COMMENT statement
3.4.2.1.
COMMENT ON TABLE
3.4.2.2.
COMMENT ON VIEW
3.4.2.3.
COMMENT ON COLUMN
3.4.3.
The CREATE statement
3.4.3.1.
CREATE DATABASE
3.4.3.2.
CREATE DOMAIN
3.4.3.3.
CREATE FUNCTION (new)
3.4.3.3.1.
Scalar functions (new)
3.4.3.3.2.
CREATE OR REPLACE FUNCTION (new)
3.4.3.3.3.
SQL data access characteristics (new)
3.4.3.3.4.
DETERMINISTIC characteristic (new)
3.4.3.3.5.
ON NULL INPUT characteristic (new)
3.4.3.4.
CREATE INDEX
3.4.3.5.
CREATE PROCEDURE (new)
3.4.3.5.1.
CREATE OR REPLACE PROCEDURE (new)
3.4.3.5.2.
SQL data access characteristics (new)
3.4.3.6.
CREATE SCHEMA
3.4.3.7.
CREATE SEQUENCE
3.4.3.7.1.
CREATE SEQUENCE IF NOT EXISTS
3.4.3.7.2.
CREATE SEQUENCE .. CACHE
3.4.3.7.3.
CREATE SEQUENCE .. CYCLE
3.4.3.7.4.
CREATE SEQUENCE .. MINVALUE
3.4.3.7.5.
CREATE SEQUENCE .. MAXVALUE
3.4.3.7.6.
CREATE SEQUENCE .. INCREMENT BY
3.4.3.7.7.
CREATE SEQUENCE .. START WITH
3.4.3.8.
CREATE TABLE
3.4.3.8.1.
Columns
3.4.3.8.2.
Nullability
3.4.3.8.3.
Defaults
3.4.3.8.4.
Identities
3.4.3.8.5.
Primary key
3.4.3.8.6.
Unique constraints
3.4.3.8.7.
Foreign keys
3.4.3.8.8.
Check constraints
3.4.3.8.9.
From a SELECT
3.4.3.8.10.
Temporary tables
3.4.3.9.
CREATE TRIGGER (new)
3.4.3.9.1.
Events (new)
3.4.3.9.2.
REFERENCING clause (new)
3.4.3.9.3.
STATEMENT vs ROW triggers (new)
3.4.3.9.4.
WHEN clause (new)
3.4.3.10.
CREATE TYPE
3.4.3.11.
CREATE VIEW
3.4.3.11.1.
CREATE OR REPLACE VIEW
3.4.3.11.2.
WITH CHECK OPTION
3.4.3.11.3.
WITH READ ONLY
3.4.4.
The DROP statement
3.4.4.1.
DROP DATABASE
3.4.4.1.1.
DROP DATABASE IF EXISTS
3.4.4.2.
DROP DOMAIN
3.4.4.2.1.
DROP DOMAIN IF EXISTS
3.4.4.3.
DROP FUNCTION (new)
3.4.4.3.1.
DROP FUNCTION IF EXISTS (new)
3.4.4.4.
DROP INDEX
3.4.4.4.1.
DROP INDEX IF EXISTS
3.4.4.5.
DROP PROCEDURE (new)
3.4.4.5.1.
DROP PROCEDURE IF EXISTS (new)
3.4.4.6.
DROP SCHEMA
3.4.4.6.1.
DROP SCHEMA IF EXISTS
3.4.4.7.
DROP SEQUENCE
3.4.4.7.1.
DROP SEQUENCE IF EXISTS
3.4.4.8.
DROP TABLE
3.4.4.8.1.
DROP TABLE IF EXISTS
3.4.4.9.
DROP TRIGGER (new)
3.4.4.9.1.
DROP TRIGGER IF EXISTS (new)
3.4.4.10.
DROP TYPE
3.4.4.10.1.
DROP TYPE IF EXISTS
3.4.4.11.
DROP VIEW
3.4.4.11.1.
DROP VIEW IF EXISTS
3.4.5.
The GRANT statement
3.4.6.
The REVOKE statement
3.4.7.
The SET statement
3.4.7.1.
SET CATALOG
3.4.7.2.
SET SCHEMA
3.4.8.
The TRUNCATE statement
3.4.9.
Generating DDL from objects

previous : next

References to this page

Feedback

Do you have any feedback about this page? We'd love to hear it!

↑ Back to top

Community

Support

Legal

Documentation

The jOOQ Logo