DDL — CREATE, ALTER, DROP

DDL vs DML — the split

CREATE TABLE — the anatomy

CREATE TABLE users (
  id           BIGSERIAL PRIMARY KEY,                    -- auto-increment + PK
  email        TEXT        NOT NULL UNIQUE,              -- can't be NULL, no duplicates
  name         VARCHAR(80) NOT NULL,
  age          INT         CHECK (age >= 0 AND age <= 150),
  country      CHAR(2)     DEFAULT 'IN',
  is_active    BOOLEAN     NOT NULL DEFAULT TRUE,
  created_at   TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
  preferences  JSONB,
  manager_id   BIGINT      REFERENCES users(id) ON DELETE SET NULL
);

Each column line has: name, type, optional constraints, optional DEFAULT.

Common data types

Rules of thumb: BIGINT for IDs, TEXT for free strings, NUMERIC for money, TIMESTAMPTZ for "when did this happen". CHAR(n) is right-padded with spaces — almost always wrong.

NOT NULL and DEFAULT

A column without NOT NULL accepts NULL. With DEFAULT, omitted inserts get the default:

INSERT INTO users (email, name) VALUES ('a@b.c', 'Ada');
-- country = 'IN', is_active = TRUE, created_at = now() — from defaults

DEFAULT accepts expressions: DEFAULT gen_random_uuid(), DEFAULT CURRENT_DATE + INTERVAL '7 days'.

ALTER TABLE — change shape after the fact

-- Add a column
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users ADD COLUMN signup_source TEXT NOT NULL DEFAULT 'web';
-- Drop a column
ALTER TABLE users DROP COLUMN phone;
-- Rename
ALTER TABLE users RENAME COLUMN name TO full_name;
ALTER TABLE users RENAME TO app_users;
-- Change type (Postgres)
ALTER TABLE users ALTER COLUMN age TYPE BIGINT;
ALTER TABLE users ALTER COLUMN age TYPE TEXT USING age::TEXT;
-- Add / drop constraints
ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);
ALTER TABLE users DROP CONSTRAINT users_email_unique;
-- Add a foreign key after the fact
ALTER TABLE posts
  ADD CONSTRAINT posts_user_fk
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

⚠️ ALTER TABLE on huge tables can lock writes — production migrations should be planned (see "concurrent" index creation, table rewrites, online schema-change tools).

DROP and TRUNCATE — destructive

DROP TABLE IF EXISTS staging_tmp;          -- no error if missing
TRUNCATE TABLE events RESTART IDENTITY;    -- wipe + reset auto IDs

DROP and TRUNCATE are easy to regret — always preview them in dev first.

Schemas, views, and indexes

-- Schemas namespace your tables
CREATE SCHEMA analytics;
CREATE TABLE analytics.events (...);
-- Views — saved SELECT, queried like a table
CREATE VIEW v_active_users AS
SELECT id, email FROM users WHERE is_active;
-- Materialized views — store the result, refresh on demand (Postgres)
CREATE MATERIALIZED VIEW mv_top_countries AS
SELECT country, COUNT(*) AS users FROM users GROUP BY country;
REFRESH MATERIALIZED VIEW mv_top_countries;
-- Indexes (covered properly in next-but-one topic)
CREATE INDEX idx_users_country ON users (country);
DROP INDEX idx_users_country;

DDL is auto-committed in MySQL — be careful

In MySQL/MariaDB, every DDL implicitly commits the open transaction. Postgres treats DDL like any other statement (transactional, can be rolled back). Test your migrations against the engine you actually use.

Migration discipline (the practical part)

Real teams don't run ALTER TABLE ad-hoc — they use migration tools (Prisma, Flyway, Alembic, Knex) that:

1. Store every schema change as a versioned file. 2. Apply them in order on every environment. 3. Track which migrations have run in a schema_migrations table.

For backwards-compatible rollouts: add new column NULL first → backfill → flip to NOT NULL instead of one big breaking change.