Data Definition Language (DDL)

This section documents standard SQL (ISO/IEC 9075-2 (SQL/Foundation)) functionality only. Vendor-specific extensions or behavior of any particular database management system are intentionally left out. In the US, the standard is also distributed via ANSI’s webstore (search "ISO/IEC 9075"). This content was generated with the assistance of AI and should be verified against your target DBMS’s own documentation before relying on it in production.

Data Definition Language statements create, modify, and remove the structures that hold data: schemas, tables, views, and indexes. This page covers the standard CREATE, ALTER, and DROP statements, followed by the constraint clauses that constrain the values a table’s columns may hold. For the authoritative (if less readable) specification, see ISO/IEC 9075-2 (SQL/Foundation), the part of the standard that defines these statements and clauses.

CREATE

CREATE SCHEMA

CREATE SCHEMA creates a named namespace that groups related tables, views, and other schema objects together.

Allowed options:

SCHEMA <name> — the schema identifier. Every unqualified object created afterwards within the schema is implicitly qualified by this name.
AUTHORIZATION <user-name> — optionally names the owner (authorization identifier) of the schema. Either the schema name, the AUTHORIZATION clause, or both must be present.
A list of CREATE TABLE, CREATE VIEW, and GRANT statements may optionally follow in the same statement, all created within the new schema.

CREATE SCHEMA sales AUTHORIZATION sales_admin;

CREATE TABLE

CREATE TABLE defines a new base table: its name, its columns (each with a data type and optional column-level constraints), and optional table-level constraints.

Allowed options:

<table-name> — the identifier for the new table, optionally schema-qualified (e.g. sales.orders).
A comma-separated list of column definitions, each consisting of:
- <column-name> — the column identifier.
- <data-type> — a standard SQL data type. At a conceptual level these fall into a few families:
  - Exact numeric: SMALLINT, INTEGER, BIGINT, NUMERIC(precision, scale), DECIMAL(precision, scale).
  - Approximate numeric: REAL, DOUBLE PRECISION, FLOAT(precision).
  - Character: CHARACTER(length) (fixed-length, blank-padded), CHARACTER VARYING(length) (variable-length), CHARACTER LARGE OBJECT (large text).
  - Binary: BINARY(length), BINARY VARYING(length), BINARY LARGE OBJECT.
  - Boolean: BOOLEAN.
  - Date and time: DATE, TIME, TIMESTAMP (each optionally WITH TIME ZONE), and INTERVAL.
- Zero or more column constraints (see the Constraints section below): NOT NULL, UNIQUE, PRIMARY KEY, CHECK (<condition>), REFERENCES <table>[(column)], DEFAULT <value>.
Zero or more table constraints — constraints not tied to a single column, such as a composite PRIMARY KEY, a composite UNIQUE, a table-level CHECK, or a FOREIGN KEY … REFERENCES … clause.
ON COMMIT { DELETE ROWS | PRESERVE ROWS } — applicable only to temporary tables (created with a CREATE GLOBAL TEMPORARY TABLE or CREATE LOCAL TEMPORARY TABLE form), controlling whether rows survive past the end of the transaction that inserted them.

CREATE TABLE sales.orders (
    order_id      INTEGER NOT NULL,
    customer_id   INTEGER NOT NULL,
    order_date    DATE NOT NULL DEFAULT CURRENT_DATE,
    status        CHARACTER VARYING(20) NOT NULL,
    total_amount  NUMERIC(12, 2) CHECK (total_amount >= 0),
    CONSTRAINT pk_orders PRIMARY KEY (order_id),
    CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id)
        REFERENCES sales.customers (customer_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

CREATE VIEW

CREATE VIEW defines a virtual table whose contents are derived, at query time, from the result of a stored SELECT statement.

Allowed options:

<view-name> — the identifier for the new view, optionally schema-qualified.
(<column-name>, …) — an optional explicit list of column names for the view; when omitted, the view’s columns take the names produced by the query.
AS <query-expression> — the SELECT statement that defines the view’s contents. This is mandatory.
WITH [CASCADED | LOCAL] CHECK OPTION — optional. When present, rows inserted or updated through the view must satisfy the view’s own WHERE condition. CASCADED (the default) also enforces the check conditions of any view this one is built on; LOCAL enforces only this view’s own condition.

CREATE VIEW sales.open_orders AS
    SELECT order_id, customer_id, order_date, total_amount
    FROM sales.orders
    WHERE status = 'OPEN'
    WITH CASCADED CHECK OPTION;

CREATE INDEX

Indexes are not part of the ISO/IEC 9075 standard: the standard leaves indexing entirely as an implementation detail, so there is no standard CREATE INDEX syntax. Every SQL product supports some form of CREATE INDEX, and the shape shown below is the syntax most implementations converge on in practice, but treat it as a de-facto convention rather than standard SQL, and check your target DBMS’s own documentation for the exact syntax and options it supports.

Commonly supported options:

UNIQUE — optional keyword requesting that the index also enforce uniqueness of the indexed column values.
<index-name> — the identifier for the new index.
ON <table-name> (<column-name> [ASC | DESC], …) — the table and ordered list of columns the index is built over.

CREATE UNIQUE INDEX idx_orders_customer_date
    ON sales.orders (customer_id, order_date DESC);

ALTER TABLE

ALTER TABLE changes the structure of an existing table: adding or dropping columns, changing a column’s definition, or adding and dropping constraints. A single ALTER TABLE statement carries exactly one such action.

ADD COLUMN

Adds a new column to an existing table.

Allowed options:

ADD [COLUMN] <column-name> <data-type> [<column-constraint> …] — the new column’s name, data type, and any column constraints (for example DEFAULT, NOT NULL).

ALTER TABLE sales.orders
    ADD COLUMN shipped_date DATE;

DROP COLUMN

Removes an existing column from a table.

Allowed options:

DROP [COLUMN] <column-name> [CASCADE | RESTRICT] — the column to remove.
- CASCADE also drops every object that depends on the column (for example a view referencing it).
- RESTRICT (the default in the standard) refuses the operation if any other object depends on the column.

ALTER TABLE sales.orders
    DROP COLUMN shipped_date RESTRICT;

ALTER/MODIFY COLUMN

Changes the definition of an existing column. The standard expresses this as several distinct, narrower ALTER COLUMN actions rather than one general-purpose MODIFY COLUMN form.

Allowed options:

ALTER [COLUMN] <column-name> SET DEFAULT <value> — sets or replaces the column’s default value.
ALTER [COLUMN] <column-name> DROP DEFAULT — removes the column’s default value.
ALTER [COLUMN] <column-name> SET NOT NULL — adds a NOT NULL constraint to the column.
ALTER [COLUMN] <column-name> DROP NOT NULL — removes the column’s NOT NULL constraint.
ALTER [COLUMN] <column-name> SET DATA TYPE <data-type> — changes the column’s declared data type.

ALTER TABLE sales.orders
    ALTER COLUMN total_amount SET DATA TYPE NUMERIC(14, 2);

ALTER TABLE sales.orders
    ALTER COLUMN status SET DEFAULT 'OPEN';

ADD / DROP constraint

Adds or removes a table-level constraint after the table has already been created.

Allowed options:

ADD CONSTRAINT <constraint-name> <constraint-definition> — adds a new named table constraint (any of the forms described in Constraints below).
ADD <constraint-definition> — adds an unnamed table constraint; the system generates an implementation defined name for it.
DROP CONSTRAINT <constraint-name> [CASCADE | RESTRICT] — removes an existing named constraint.
- CASCADE also drops every object whose definition depends on the constraint.
- RESTRICT (the default in the standard) refuses the operation if any other object depends on the constraint.

ALTER TABLE sales.orders
    ADD CONSTRAINT chk_orders_status CHECK (status IN ('OPEN', 'SHIPPED', 'CANCELLED'));

ALTER TABLE sales.orders
    DROP CONSTRAINT chk_orders_status RESTRICT;

DROP

Removes an existing schema object. The standard DROP forms share the same overall shape: the object kind, its name, and a CASCADE/RESTRICT clause governing what happens to dependent objects.

CASCADE — also drops every object that depends on the one being dropped (for example, views built on a dropped table, or indexes and constraints on a dropped table).
RESTRICT — refuses the operation if any other object depends on the one being dropped. This is the default behavior in the standard when neither keyword is specified.

DROP TABLE

DROP TABLE sales.orders CASCADE;

DROP VIEW

DROP VIEW sales.open_orders RESTRICT;

DROP INDEX

As noted under CREATE INDEX, indexes are outside the scope of the ISO/IEC 9075 standard, so there is no standard DROP INDEX syntax either. The form below reflects the common convention; consult your target DBMS’s documentation for its exact syntax.

DROP INDEX idx_orders_customer_date;

DROP SCHEMA

DROP SCHEMA <schema-name> CASCADE — also drops every object contained in the schema (tables, views, and so on).
DROP SCHEMA <schema-name> RESTRICT — refuses the operation if the schema still contains any object.

DROP SCHEMA sales CASCADE;

Constraints

Constraints restrict the values a column or a row may hold. They can be declared inline as column constraints (immediately after a single column’s data type) or separately as table constraints (referring to one or more columns by name), and either form may optionally be named with CONSTRAINT <constraint-name> so it can later be dropped or altered by name.

PRIMARY KEY

Declares one column, or a comma-separated set of columns, whose combined values uniquely identify each row in the table. A table may have at most one primary key. Every column that participates in a primary key is implicitly NOT NULL, and the combination of values across the primary key columns must be unique.

CREATE TABLE sales.order_items (
    order_id     INTEGER NOT NULL,
    line_number  INTEGER NOT NULL,
    product_id   INTEGER NOT NULL,
    quantity     INTEGER NOT NULL CHECK (quantity > 0),
    CONSTRAINT pk_order_items PRIMARY KEY (order_id, line_number)
);

FOREIGN KEY

Declares that the value(s) of one column, or set of columns, must match a value that exists in the referenced table’s primary key or a UNIQUE constraint of that table (or be entirely NULL, unless NOT NULL is also specified).

Allowed clauses:

FOREIGN KEY (<column>, …) REFERENCES <table-name> [(<column>, …)] — the referencing column(s) and the referenced table. When the referenced column list is omitted, the referenced table’s primary key is used.
ON DELETE <referential-action> — the action taken when a referenced row is deleted.
ON UPDATE <referential-action> — the action taken when a referenced row’s key value is updated.
<referential-action> is one of:
- CASCADE — propagate the delete/update to the referencing rows.
- SET NULL — set the referencing column(s) to NULL.
- SET DEFAULT — set the referencing column(s) to their default value.
- RESTRICT — refuse the delete/update immediately if any referencing row exists.
- NO ACTION (the default when neither clause is specified) — refuse the delete/update if any referencing row still exists once all other referential actions of the same statement have been applied.

CREATE TABLE sales.order_items (
    order_id     INTEGER NOT NULL,
    line_number  INTEGER NOT NULL,
    product_id   INTEGER NOT NULL,
    quantity     INTEGER NOT NULL CHECK (quantity > 0),
    CONSTRAINT pk_order_items PRIMARY KEY (order_id, line_number),
    CONSTRAINT fk_order_items_order FOREIGN KEY (order_id)
        REFERENCES sales.orders (order_id)
        ON DELETE CASCADE
        ON UPDATE NO ACTION
);

UNIQUE

Declares that the combination of values in one column, or a comma-separated set of columns, must be unique across every row of the table. Unlike PRIMARY KEY, a table may declare any number of UNIQUE constraints, and the participating columns are not automatically made NOT NULL; standard SQL permits multiple rows with NULL in a UNIQUE column, since two NULL values are never considered equal to one another.

CREATE TABLE sales.customers (
    customer_id INTEGER NOT NULL,
    email       CHARACTER VARYING(255),
    CONSTRAINT pk_customers PRIMARY KEY (customer_id),
    CONSTRAINT uq_customers_email UNIQUE (email)
);

NOT NULL

A column constraint that forbids the column from holding a NULL value in any row. It can only be declared as a column constraint, not as a separate table constraint.

CREATE TABLE sales.customers (
    customer_id INTEGER NOT NULL,
    full_name   CHARACTER VARYING(120) NOT NULL
);

CHECK

Declares a boolean <condition> that every row of the table must satisfy (the row is rejected if the condition evaluates to FALSE; a condition that evaluates to UNKNOWN, for example because it involves a NULL, does not violate the constraint). It may reference a single column, as a column constraint, or multiple columns, as a table constraint.

CREATE TABLE sales.orders (
    order_id      INTEGER NOT NULL,
    order_date    DATE NOT NULL,
    ship_date     DATE,
    total_amount  NUMERIC(12, 2),
    CONSTRAINT pk_orders PRIMARY KEY (order_id),
    CONSTRAINT chk_orders_amount CHECK (total_amount >= 0),
    CONSTRAINT chk_orders_dates CHECK (ship_date IS NULL OR ship_date >= order_date)
);

DEFAULT

A column constraint that supplies the value a column takes when an INSERT statement does not explicitly provide one. The default value may be a literal, a niladic datetime function such as CURRENT_DATE, CURRENT_TIME, or CURRENT_TIMESTAMP, NULL, or the implementation-defined system default.

CREATE TABLE sales.orders (
    order_id    INTEGER NOT NULL,
    order_date  DATE NOT NULL DEFAULT CURRENT_DATE,
    status      CHARACTER VARYING(20) NOT NULL DEFAULT 'OPEN',
    CONSTRAINT pk_orders PRIMARY KEY (order_id)
);