Data Definition Language (DDL)

What is DDL?

Data Definition Language (DDL) is the subset of SQL used to define and manage database structures — tables, columns, indexes, schemas, and constraints. The main DDL commands are CREATE, ALTER, and DROP. Unlike SELECT or INSERT, DDL statements change the shape of the database itself, not its data. Getting DDL right matters because structural mistakes are harder to fix than data mistakes — adding a constraint or changing a type on a large table requires careful migration planning.

CREATE TABLE

CREATE TABLE defines a new table, its columns, and their constraints. The constraint declarations are especially important: they let the database enforce rules that would otherwise require application-level validation, making your data integrity guarantees stronger and independent of which client touches the database.

CREATE TABLE employees (
    id          BIGSERIAL PRIMARY KEY,
    first_name  TEXT NOT NULL,
    last_name   TEXT NOT NULL,
    email       TEXT UNIQUE NOT NULL,
    salary      NUMERIC(12, 2),
    hired_at    DATE NOT NULL DEFAULT CURRENT_DATE,
    department  TEXT,
    is_active   BOOLEAN NOT NULL DEFAULT TRUE
);

Each column definition has the form: column_name data_type [constraints].

Primary Keys

A primary key uniquely identifies each row and forms the anchor for all foreign key relationships. It implicitly adds NOT NULL and a unique index — PostgreSQL uses this index to enforce uniqueness and to speed up joins that reference this table.

-- Single-column primary key (most common)
CREATE TABLE products (
    id   SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

-- Composite primary key: the combination of both columns must be unique
-- Used for junction tables where neither column alone is unique
CREATE TABLE order_items (
    order_id   INTEGER,
    product_id INTEGER,
    quantity   INTEGER NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

Foreign Keys

Foreign keys enforce referential integrity — they prevent rows from referencing data that doesn’t exist, and control what happens to child rows when their parent is deleted. Without foreign keys, orphaned rows accumulate silently and queries that join related tables produce incorrect results.

CREATE TABLE orders (
    id          BIGSERIAL PRIMARY KEY,
    customer_id BIGINT NOT NULL REFERENCES customers(id),
    created_at  TIMESTAMPTZ DEFAULT NOW()
);

The ON DELETE clause controls what happens to child rows when a parent row is deleted:

CREATE TABLE order_items (
    id         BIGSERIAL PRIMARY KEY,
    order_id   BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,   -- delete items with order
    product_id BIGINT NOT NULL REFERENCES products(id) ON DELETE RESTRICT, -- block if items exist
    quantity   INTEGER NOT NULL
);

• ON DELETE CASCADE — delete child rows automatically when the parent is deleted
• ON DELETE RESTRICT — prevent deletion of the parent if child rows exist (default behavior)
• ON DELETE SET NULL — set the foreign key column to NULL when the parent is deleted
• ON DELETE SET DEFAULT — set the foreign key column to its default value

Other Constraints

CHECK constraints enforce business rules at the database level — rules like “price must be positive” or “stock can’t go negative”. Naming constraints makes error messages much clearer when they’re violated, and lets you drop or modify individual constraints by name later.

CREATE TABLE products (
    id          SERIAL PRIMARY KEY,
    name        TEXT NOT NULL,
    price       NUMERIC(10, 2) NOT NULL,
    stock       INTEGER NOT NULL DEFAULT 0,
    sku         TEXT UNIQUE,
    category    TEXT NOT NULL DEFAULT 'general',

    -- Named constraints produce helpful error messages
    CONSTRAINT price_positive    CHECK (price > 0),
    CONSTRAINT stock_non_negative CHECK (stock >= 0)
);

IF NOT EXISTS

Scripts that set up a database from scratch often run multiple times — during development, in CI, across multiple environments. IF NOT EXISTS makes CREATE TABLE idempotent so it doesn’t error when the table already exists.

CREATE TABLE IF NOT EXISTS audit_log (
    id         BIGSERIAL PRIMARY KEY,
    table_name TEXT NOT NULL,
    action     TEXT NOT NULL,
    changed_at TIMESTAMPTZ DEFAULT NOW()
);

ALTER TABLE

Schemas evolve. ALTER TABLE lets you modify an existing table without losing data — adding columns, changing types, renaming things, or adjusting constraints. In production, most ALTER TABLE operations should be part of a versioned migration script, not run manually.

Adding Columns

-- New nullable column — safe to add to any table including large ones
ALTER TABLE employees ADD COLUMN phone TEXT;

-- Self-referencing foreign key for manager relationships
ALTER TABLE employees ADD COLUMN manager_id BIGINT REFERENCES employees(id);

Dropping Columns

-- Remove a column and its data permanently
ALTER TABLE employees DROP COLUMN phone;

-- If other objects depend on this column, CASCADE drops them too
ALTER TABLE employees DROP COLUMN department CASCADE;

Changing Column Types

PostgreSQL will attempt to cast existing values to the new type automatically. When the cast is ambiguous or non-obvious, provide an explicit USING expression so there’s no ambiguity about how old values should be converted.

ALTER TABLE products ALTER COLUMN price TYPE NUMERIC(14, 2);

-- Provide an explicit cast expression when needed
ALTER TABLE legacy_data ALTER COLUMN created_at TYPE TIMESTAMPTZ
    USING created_at::TIMESTAMPTZ;

Renaming

ALTER TABLE employees RENAME COLUMN hired_at TO start_date;
ALTER TABLE employees RENAME TO staff;

Adding and Dropping Constraints

-- Add a NOT NULL constraint (the column must have no NULLs already)
ALTER TABLE employees ALTER COLUMN salary SET NOT NULL;

-- Remove NOT NULL to allow missing values
ALTER TABLE employees ALTER COLUMN salary DROP NOT NULL;

-- Add a named CHECK constraint — the regex enforces a specific SKU format
ALTER TABLE products ADD CONSTRAINT sku_format CHECK (sku ~ '^[A-Z]{3}-[0-9]{4}$');

-- Drop a named constraint by its name
ALTER TABLE products DROP CONSTRAINT sku_format;

-- Add a DEFAULT for new rows
ALTER TABLE employees ALTER COLUMN is_active SET DEFAULT TRUE;

Adding a NOT NULL Column to an Existing Table

This is a common migration challenge. If a table already has rows, adding NOT NULL without a default fails — PostgreSQL would have nothing to put in existing rows. The safe pattern is to add the column as nullable, backfill it, then add the constraint.

-- This fails if the table has data:
ALTER TABLE employees ADD COLUMN department_id INTEGER NOT NULL; -- ERROR

-- Safe approach: add with a temporary default, then remove the default
ALTER TABLE employees ADD COLUMN department_id INTEGER NOT NULL DEFAULT 1;
ALTER TABLE employees ALTER COLUMN department_id DROP DEFAULT;

DROP TABLE

DROP TABLE removes the table and all its data permanently. There’s no undo outside of a transaction or a backup. Use IF EXISTS in scripts to avoid errors when the table might not exist.

DROP TABLE audit_log;

-- Avoid errors if the table doesn't exist
DROP TABLE IF EXISTS audit_log;

-- Drop a table that is referenced by foreign keys in other tables
-- CASCADE also drops the dependent foreign key constraints
DROP TABLE customers CASCADE;

TRUNCATE

TRUNCATE removes all rows from a table instantly without scanning them individually. It’s far faster than DELETE FROM table for large tables because it operates at the storage level rather than row by row. Use it when you need to clear a table completely and speed matters.

TRUNCATE TABLE audit_log;

-- Reset the auto-increment counter too (useful in test environments)
TRUNCATE TABLE products RESTART IDENTITY;

-- Truncate multiple related tables at once
TRUNCATE TABLE order_items, orders RESTART IDENTITY CASCADE;

Schemas

Schemas are namespaces within a database — a way to organize tables into logical groups without creating separate databases. The default schema is public. Schemas are useful for separating concerns: application tables in one schema, reporting tables in another, or one schema per tenant in a multi-tenant system.

-- Create a separate namespace for reporting tables
CREATE SCHEMA reporting;

-- Create a table inside the schema
CREATE TABLE reporting.monthly_summary (
    month       DATE PRIMARY KEY,
    revenue     NUMERIC(14, 2),
    order_count INTEGER
);

-- Reference it with the schema prefix
SELECT * FROM reporting.monthly_summary WHERE month = '2024-01-01';

Generated Columns

Generated columns compute their value automatically from other columns in the same row. They eliminate the need to maintain derived values in application code — the database keeps them consistent no matter which client writes the row. They’re declared as GENERATED ALWAYS AS (expression) STORED.

CREATE TABLE rectangles (
    id     SERIAL PRIMARY KEY,
    width  NUMERIC NOT NULL,
    height NUMERIC NOT NULL,
    -- area is automatically computed; you cannot INSERT or UPDATE it directly
    area   NUMERIC GENERATED ALWAYS AS (width * height) STORED
);

INSERT INTO rectangles (width, height) VALUES (5, 8);
SELECT * FROM rectangles;
-- area is automatically 40