Schema (DDL)
Data Definition Language: the statements that change the shape of the database rather than the rows in it. Create and drop tables, add and rename columns, define keys and indexes, build views. Use these when the data model itself needs to change, not when you're just adding or fixing rows.
At a glance #
sql
CREATE TABLE app_users (id INTEGER PRIMARY KEY, email VARCHAR NOT NULL, deleted_at TIMESTAMP);
ALTER TABLE app_users ADD COLUMN signup_source VARCHAR;
ALTER TABLE app_users RENAME COLUMN signup_source TO source;
CREATE UNIQUE INDEX app_users_email_idx ON app_users (email);
CREATE VIEW active_users AS SELECT * FROM app_users WHERE deleted_at IS NULL;
DROP TABLE IF EXISTS users_legacy;Pages #
- CREATE TABLE: define a new table with columns, types, defaults, and inline constraints.
- ALTER TABLE ADD COLUMN: add a column to an existing table without rebuilding it.
- ALTER TABLE RENAME: rename a table or a column safely.
- DROP TABLE: remove a table (and the patterns that keep it from blowing up in production).
- PRIMARY KEY: declare the unique row identifier.
- FOREIGN KEY: link a child column to a parent's primary key, with cascade options.
- CREATE INDEX: speed up lookups; understand when an index helps and when it doesn't.
- CREATE VIEW: save a query as a named virtual table.
Tip
DDL in production is a different sport. A CREATE TABLE in a notebook is harmless; the same statement against a busy production schema can lock writers, break deploys, or leave half-migrated state. When you cross from "writing SQL" into "changing the database," lean on IF EXISTS / IF NOT EXISTS guards, run migrations through tooling (Liquibase, Flyway, dbt, Alembic) rather than ad-hoc, and always have a rollback path.