SQL Data Definition Language (DDL)

DDL commands define and modify the structure of your database — creating tables, changing columns, adding constraints, and removing objects. Unlike DML (INSERT, UPDATE, DELETE), DDL changes the schema itself, not the data within it.

In most databases, DDL statements are auto-committed and cannot be rolled back (PostgreSQL is an exception — DDL is transactional there).

CREATE TABLE

Creates a new table with column definitions and constraints:

CREATE TABLE products (
    product_id   SERIAL PRIMARY KEY,
    sku          VARCHAR(50)  UNIQUE NOT NULL,
    name         VARCHAR(255) NOT NULL,
    description  TEXT,
    price        NUMERIC(10, 2) NOT NULL CHECK (price >= 0),
    category_id  INTEGER REFERENCES categories(category_id),
    is_active    BOOLEAN DEFAULT TRUE,
    created_at   TIMESTAMPTZ DEFAULT NOW(),
    updated_at   TIMESTAMPTZ DEFAULT NOW()
);

Column definition anatomy:

column_name  data_type  [constraint] [default]

CREATE TABLE IF NOT EXISTS — prevents an error if the table already exists:

CREATE TABLE IF NOT EXISTS audit_log (
    log_id     BIGSERIAL PRIMARY KEY,
    table_name VARCHAR(100),
    action     VARCHAR(10),
    changed_at TIMESTAMPTZ DEFAULT NOW()
);

ALTER TABLE

Modifies an existing table’s structure:

Add a column:

ALTER TABLE customers ADD COLUMN phone VARCHAR(20);
ALTER TABLE orders ADD COLUMN tracking_number VARCHAR(100) DEFAULT NULL;

Drop a column:

ALTER TABLE products DROP COLUMN legacy_code;
ALTER TABLE products DROP COLUMN IF EXISTS legacy_code;  -- won't error if absent

Rename a column:

ALTER TABLE customers RENAME COLUMN surname TO last_name;

Change a column’s data type:

ALTER TABLE products ALTER COLUMN price TYPE NUMERIC(12, 4);
-- Note: type changes can fail if existing data can't be cast to the new type

Add a constraint:

ALTER TABLE orders ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

ALTER TABLE products ADD CONSTRAINT chk_price_positive
    CHECK (price > 0);

ALTER TABLE customers ADD CONSTRAINT uq_customers_email
    UNIQUE (email);

Drop a constraint:

ALTER TABLE orders DROP CONSTRAINT fk_orders_customer;

Rename a table:

ALTER TABLE order_items RENAME TO line_items;

DROP TABLE

Permanently removes a table and all its data:

DROP TABLE staging_data;
DROP TABLE IF EXISTS staging_data;  -- won't error if table doesn't exist

-- CASCADE removes dependent objects (views, FK constraints in other tables)
DROP TABLE customers CASCADE;

DROP TABLE is irreversible. In production, consider renaming first and dropping after a safe observation period.

CREATE INDEX

Indexes speed up reads on frequently queried columns:

-- Single column index
CREATE INDEX idx_orders_customer ON orders(customer_id);

-- Composite index (multi-column)
CREATE INDEX idx_orders_status_date ON orders(status, created_at DESC);

-- Unique index (also enforces uniqueness as a constraint)
CREATE UNIQUE INDEX idx_products_sku ON products(sku);

-- Partial index (only indexes rows matching a condition)
CREATE INDEX idx_orders_pending ON orders(customer_id)
    WHERE status = 'pending';

-- If you don't want to lock the table during index creation (PostgreSQL):
CREATE INDEX CONCURRENTLY idx_orders_customer ON orders(customer_id);

CREATE VIEW

A view is a saved query treated as a virtual table:

CREATE VIEW customer_order_summary AS
SELECT
    c.customer_id,
    c.name,
    c.email,
    COUNT(o.order_id) AS order_count,
    COALESCE(SUM(o.amount), 0) AS lifetime_value
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name, c.email;

-- Query the view like a table
SELECT * FROM customer_order_summary WHERE lifetime_value > 1000;

Views don’t store data — they execute the underlying query each time they’re queried. For performance on large datasets, use materialized views (PostgreSQL) or create summary tables.

TRUNCATE TABLE

Removes all rows from a table without removing the table itself. Faster than DELETE for clearing entire tables:

TRUNCATE TABLE staging_events;

-- Reset auto-increment sequences (PostgreSQL)
TRUNCATE TABLE staging_events RESTART IDENTITY;

-- Truncate with cascade (also clears child tables)
TRUNCATE TABLE customers CASCADE;

CREATE SCHEMA

Schemas are namespaces within a database for organizing tables:

CREATE SCHEMA IF NOT EXISTS reporting;
CREATE SCHEMA IF NOT EXISTS staging;

-- Create a table in a specific schema
CREATE TABLE reporting.daily_sales (
    report_date DATE,
    total_revenue NUMERIC(15, 2)
);

-- Reference with schema prefix
SELECT * FROM reporting.daily_sales;

DDL Execution Order for Dependencies

When creating interdependent objects, create parent objects before children:

-- 1. Create parent table first
CREATE TABLE categories (
    category_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

-- 2. Then create child table (references parent)
CREATE TABLE products (
    product_id  SERIAL PRIMARY KEY,
    name        VARCHAR(255) NOT NULL,
    category_id INTEGER REFERENCES categories(category_id)
);

And when dropping, reverse the order (or use CASCADE):

-- Drop child first (or CASCADE from parent)
DROP TABLE products;
DROP TABLE categories;

DDL in Schema Migrations

In production, DDL changes are managed through migration tools (Flyway, Liquibase, Alembic, Django migrations, dbt). Key practices:

Version every DDL change — each migration is a numbered file
Make changes additive where possible — adding columns is safer than dropping
Test in staging first — DDL on large tables can lock them and impact production
Use IF EXISTS / IF NOT EXISTS — makes scripts idempotent (safe to run twice)

-- V20250601__add_tracking_number.sql
ALTER TABLE orders ADD COLUMN IF NOT EXISTS tracking_number VARCHAR(100);
CREATE INDEX IF NOT EXISTS idx_orders_tracking ON orders(tracking_number)
    WHERE tracking_number IS NOT NULL;