SQL DDL — CREATE, ALTER, and DROP Tables Safely

What DDL is

Data Definition Language (DDL) is the subset of SQL that creates, modifies, and deletes database objects — tables, indexes, views, sequences. Unlike SELECT or INSERT, DDL changes the structure, not the data. Most DDL statements are auto-committed (no rollback in MySQL/SQL Server), though Postgres wraps DDL in transactions, letting you roll back a botched migration.

CREATE TABLE

CREATE TABLE customers (
    id           BIGINT       PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    email        VARCHAR(254) NOT NULL UNIQUE,
    display_name VARCHAR(100) NOT NULL,
    country_code CHAR(2)      NOT NULL DEFAULT 'US',
    created_at   TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_at   TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE TABLE orders (
    id           BIGINT       PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    customer_id  BIGINT       NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
    status       VARCHAR(20)  NOT NULL DEFAULT 'pending',
    total_amount NUMERIC(10,2) NOT NULL,
    created_at   TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

Always define NOT NULL constraints at creation time — backfilling them later on a large table requires a full scan and a lock. Use GENERATED ALWAYS AS IDENTITY (Postgres) or AUTO_INCREMENT (MySQL) for surrogate primary keys.

CREATE TABLE AS SELECT — cloning data

-- Create a reporting snapshot table from a complex query
CREATE TABLE monthly_summary_2026_05 AS
SELECT
    customer_id,
    COUNT(*)          AS order_count,
    SUM(total_amount) AS total_spent
FROM orders
WHERE created_at BETWEEN '2026-05-01' AND '2026-05-31'
GROUP BY customer_id;

The new table inherits column names and types from the query but not constraints or indexes. Add them explicitly afterwards.

ALTER TABLE — safe schema changes

ALTER TABLE is the riskiest DDL to run on production tables because some operations acquire an ACCESS EXCLUSIVE lock that blocks all reads and writes.

-- ADD COLUMN: fast in Postgres if the default is constant (no rewrite needed)
ALTER TABLE customers ADD COLUMN phone VARCHAR(20);
ALTER TABLE orders    ADD COLUMN shipping_address_id BIGINT;

-- ADD COLUMN with a non-trivial default: triggers a table rewrite in Postgres < 11
-- Postgres 11+ handles constant defaults without a rewrite
ALTER TABLE products ADD COLUMN is_archived BOOLEAN NOT NULL DEFAULT FALSE;

-- RENAME COLUMN: instant, metadata-only
ALTER TABLE customers RENAME COLUMN display_name TO full_name;

-- CHANGE TYPE: may require a full table rewrite and a lock
-- Do this in a maintenance window or use a multi-step online migration
ALTER TABLE orders ALTER COLUMN total_amount TYPE NUMERIC(12, 2);

Safe schema changes on large tables

For tables with millions of rows, lock-heavy ALTER TABLE operations need to be done in multiple steps using non-locking alternatives:

-- Goal: add a NOT NULL column to a large orders table
-- Step 1: add the column nullable (fast, no lock)
ALTER TABLE orders ADD COLUMN fulfilled_at TIMESTAMPTZ;

-- Step 2: backfill in batches (no table lock; runs over time)
UPDATE orders SET fulfilled_at = created_at
WHERE fulfilled_at IS NULL AND status = 'delivered' AND id BETWEEN 1 AND 100000;
-- Repeat for subsequent batches...

-- Step 3: add the NOT NULL constraint as NOT VALID (fast, no full scan)
ALTER TABLE orders ADD CONSTRAINT orders_fulfilled_at_not_null
    CHECK (fulfilled_at IS NOT NULL) NOT VALID;

-- Step 4: validate (scans table, but only takes SHARE UPDATE EXCLUSIVE lock)
ALTER TABLE orders VALIDATE CONSTRAINT orders_fulfilled_at_not_null;

DROP TABLE

DROP TABLE is permanent and immediate. Always verify the table name before running. Use IF EXISTS to avoid errors in scripts.

-- Safe: no error if the table does not exist
DROP TABLE IF EXISTS staging_import_temp;

-- Cascade: drops all dependent views, foreign keys, and indexes
DROP TABLE IF EXISTS products CASCADE;

-- Do NOT run DROP TABLE on production without a backup

TRUNCATE TABLE removes all rows without logging each deletion (fast), but unlike DELETE, it cannot be rolled back in MySQL.

Naming conventions

Consistent naming avoids confusion in large schemas:

-- Tables: plural snake_case
CREATE TABLE order_items (...);
CREATE TABLE product_categories (...);

-- Primary keys: id or <table_singular>_id
-- Foreign keys: <referenced_table_singular>_id
-- Indexes: idx_<table>_<columns>
CREATE INDEX idx_orders_customer_created ON orders (customer_id, created_at DESC);

-- Constraints: <table>_<columns>_<type>
ALTER TABLE orders ADD CONSTRAINT orders_status_check
    CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled'));

Recap

CREATE TABLE is the only risk-free DDL — get types and NOT NULL constraints right at creation time. ALTER TABLE can lock large tables; use staged, non-locking migration techniques for production schema changes. DROP TABLE is irreversible — always check the table name and have a backup. Wrap DDL migrations in transactions (Postgres) so a failed migration rolls back cleanly rather than leaving the schema in a partial state.

More ways to practice