Cheat SheetsSQLSchema & DDL

Schema & DDL — Cheat Sheet

SQL · 7 topics. Download the PDF or the Instagram carousel and share it.

Cheat Sheet · AiCanCode.org
Schema & DDL
SQL7 topicsQuick revision reference
1

CREATE TABLE, Data Types & Constraints

CREATE TABLE defines a table's structure, column data types, and integrity constraints like NOT NULL, UNIQUE, DEFAULT, and CHECK.

  • ✓NOT NULL, UNIQUE, DEFAULT, and CHECK are enforced by the DB engine at write time.
  • ✓Use NUMERIC/DECIMAL for monetary values; never FLOAT or DOUBLE.
  • ✓BIGSERIAL (PostgreSQL) / BIGINT AUTO_INCREMENT (MySQL) for surrogate PKs in high-volume tables.
  • ✓Table-level constraints are required when a constraint spans multiple columns.
  • ✓Mirror DB constraints with Bean Validation annotations in JPA to fail fast at the app layer.
  • ✓TIMESTAMPTZ (timestamp with time zone) prevents bugs in multi-region deployments.
SQL — CREATE TABLE with all constraint types
-- PostgreSQL / MySQL
CREATE TABLE users (
    id          BIGSERIAL PRIMARY KEY,           -- auto-increment PK
    email       VARCHAR(255) NOT NULL UNIQUE,    -- inline NOT NULL + UNIQUE
    username    VARCHAR(50)  NOT NULL,
    status      VARCHAR(20)  NOT NULL DEFAULT 'active',
    age         SMALLINT     CHECK (age >= 0 AND age <= 150),
    balance     NUMERIC(12,2) NOT NULL DEFAULT 0.00,
    created_at  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Table-level CHECK for multi-column rule
CREATE TABLE orders (
    id           BIGSERIAL PRIMARY KEY,
    user_id      BIGINT NOT NULL,
    total_amount NUMERIC(12,2) NOT NULL,
    discount     NUMERIC(12,2) NOT NULL DEFAULT 0,
    CONSTRAINT chk_discount CHECK (discount <= total_amount)
);
2

Primary Key & Foreign Key Constraints

A primary key uniquely identifies each row; a foreign key enforces referential integrity by linking a column to the primary key of another table.

  • ✓PRIMARY KEY = NOT NULL + UNIQUE; the DB automatically indexes it.
  • ✓FOREIGN KEY prevents orphaned rows and is enforced at the storage engine level.
  • ✓ON DELETE CASCADE removes children automatically — use carefully in complex hierarchies.
  • ✓ON DELETE SET NULL is useful when child records should survive parent deletion.
  • ✓Always name FK constraints explicitly (fk_table_column) for readable error messages.
  • ✓In JPA, FetchType.LAZY on @ManyToOne prevents N+1 query explosions.
SQL — FK with ON DELETE/ON UPDATE actions
-- PostgreSQL / MySQL
CREATE TABLE departments (
    id   BIGSERIAL    PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE employees (
    id            BIGSERIAL    PRIMARY KEY,
    full_name     VARCHAR(200) NOT NULL,
    department_id BIGINT       NOT NULL,
    manager_id    BIGINT,                          -- nullable self-reference
    hired_at      DATE         NOT NULL,

    CONSTRAINT fk_emp_dept
        FOREIGN KEY (department_id) REFERENCES departments(id)
        ON DELETE RESTRICT    -- block dept deletion if employees exist
        ON UPDATE CASCADE,    -- propagate dept PK change (rare but safe)

    CONSTRAINT fk_emp_manager
        FOREIGN KEY (manager_id) REFERENCES employees(id)
        ON DELETE SET NULL    -- manager leaves → employees become unmanaged
);
3

ALTER TABLE

ALTER TABLE modifies an existing table's structure — adding, dropping, or modifying columns, and adding or dropping constraints without recreating the table.

  • ✓Adding a nullable column with no DEFAULT is a metadata-only change in PostgreSQL — instant.
  • ✓Adding a NOT NULL column with a volatile DEFAULT triggers a full table rewrite (pre-PG11).
  • ✓Use the expand-contract pattern for large table migrations to avoid long locks.
  • ✓Always wrap schema changes in a migration tool (Flyway/Liquibase) for versioning and rollback.
  • ✓PostgreSQL NOT VALID + VALIDATE CONSTRAINT splits constraint addition into two low-impact steps.
  • ✓MySQL ALGORITHM=INPLACE avoids full table copies for many DDL operations.
SQL — ALTER TABLE operations (PostgreSQL)
-- Add a nullable column (instant in PostgreSQL — no table rewrite)
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Add a NOT NULL column with a default (PostgreSQL 11+: instant for constant defaults)
ALTER TABLE users ADD COLUMN tier VARCHAR(20) NOT NULL DEFAULT 'free';

-- Drop a column
ALTER TABLE users DROP COLUMN phone;

-- Rename a column (PostgreSQL)
ALTER TABLE users RENAME COLUMN username TO login_name;

-- Change data type (may require a CAST and locks the table)
ALTER TABLE products ALTER COLUMN price TYPE NUMERIC(14,2);

-- Add a constraint after the fact
ALTER TABLE orders
    ADD CONSTRAINT fk_orders_user
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

-- Drop a constraint
ALTER TABLE orders DROP CONSTRAINT fk_orders_user;

-- Add a NOT NULL constraint (validates existing data — may be slow on large tables)
ALTER TABLE employees ALTER COLUMN department_id SET NOT NULL;
4

Creating Indexes

Indexes speed up read queries by creating an ordered data structure; CREATE INDEX, UNIQUE INDEX, and composite indexes must be chosen deliberately to avoid write overhead.

  • ✓B-Tree indexes support =, <, >, BETWEEN, and LIKE 'prefix%' predicates.
  • ✓Composite index column order matters: equality columns first, range column last.
  • ✓CREATE INDEX CONCURRENTLY avoids write-blocking in PostgreSQL.
  • ✓Partial indexes are smaller and faster for queries with a fixed WHERE condition.
  • ✓Expression indexes (on LOWER(col)) are required for case-insensitive lookups to use an index.
  • ✓Every index adds overhead to INSERT/UPDATE/DELETE — audit and drop unused indexes regularly.
SQL — index creation patterns (PostgreSQL)
-- Single-column index on a foreign key (always index FK columns)
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Unique index (also enforces uniqueness — same as UNIQUE constraint)
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- Composite index: equality first, then range
-- Serves: WHERE status = 'pending' AND created_at > '2024-01-01'
CREATE INDEX idx_orders_status_created ON orders(status, created_at);

-- Partial index: only index rows matching a condition (smaller, faster)
CREATE INDEX idx_orders_pending ON orders(user_id)
    WHERE status = 'pending';

-- Expression index: index the result of a function
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
-- Enables: WHERE LOWER(email) = 'alice@example.com' to use the index

-- Concurrent index build (PostgreSQL) — no write lock on the table
CREATE INDEX CONCURRENTLY idx_orders_product_id ON orders(product_id);
5

Views & Materialized Views

A view is a named query stored in the catalog; a materialized view caches the result set on disk and must be refreshed to reflect underlying data changes.

  • ✓Regular views are rewritten inline at query time — no storage, always fresh.
  • ✓Materialized views store data on disk — must be refreshed to reflect changes.
  • ✓REFRESH MATERIALIZED VIEW CONCURRENTLY requires a UNIQUE index but does not block reads.
  • ✓Views are used for security (column/row-level access control) and query simplification.
  • ✓You can index a materialized view just like a regular table.
  • ✓MySQL has no native materialized views — emulate with tables + scheduled events or triggers.
SQL — CREATE VIEW and access control
-- Create a view for the sales dashboard (hides internal columns)
CREATE OR REPLACE VIEW v_order_summary AS
SELECT
    o.id          AS order_id,
    u.email       AS customer_email,
    o.total_amount,
    o.status,
    o.created_at
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status != 'cancelled';

-- Query the view exactly like a table
SELECT * FROM v_order_summary WHERE status = 'pending';

-- Security: grant access to view but not underlying tables
GRANT SELECT ON v_order_summary TO reporting_role;

-- Updatable view (simple, single-table, no aggregation)
CREATE VIEW v_active_users AS
SELECT id, email, username FROM users WHERE status = 'active';
-- This INSERT goes through to the base table:
INSERT INTO v_active_users(email, username) VALUES ('bob@x.com', 'bob');
6

Stored Procedures & Functions in SQL

Stored procedures and user-defined functions encapsulate reusable SQL logic in the database, reducing round-trips and enforcing consistent business rules at the data layer.

  • ✓Functions return a value and can be used in SELECT; procedures cannot be used in expressions.
  • ✓Stored procedures reduce network round-trips by executing multi-step logic server-side.
  • ✓PL/pgSQL STABLE/IMMUTABLE hints allow the query planner to cache or inline function results.
  • ✓PostgreSQL 11+ procedures support COMMIT/ROLLBACK inside the procedure body.
  • ✓Avoid stored procedures for business logic requiring unit tests or multi-language consumers.
  • ✓Always version-control stored procedures alongside application code in migration scripts.
SQL — function and procedure in PostgreSQL
-- PostgreSQL: simple function returning a scalar
CREATE OR REPLACE FUNCTION get_user_order_count(p_user_id BIGINT)
RETURNS INTEGER
LANGUAGE plpgsql
STABLE  -- hint: result is same within a single transaction for same args
AS $$
DECLARE
    v_count INTEGER;
BEGIN
    SELECT COUNT(*) INTO v_count
    FROM orders
    WHERE user_id = p_user_id AND status != 'cancelled';
    RETURN v_count;
END;
$$;

-- Usage in a SELECT
SELECT email, get_user_order_count(id) AS order_count
FROM users WHERE status = 'active';

-- PostgreSQL: procedure with transaction control (PG11+)
CREATE OR REPLACE PROCEDURE transfer_funds(
    p_from_user BIGINT,
    p_to_user   BIGINT,
    p_amount    NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE accounts SET balance = balance - p_amount WHERE user_id = p_from_user;
    UPDATE accounts SET balance = balance + p_amount WHERE user_id = p_to_user;
    -- COMMIT is implicit at procedure end unless ROLLBACK is called
END;
$$;

CALL transfer_funds(101, 202, 500.00);
7

Transactions & Savepoints in SQL

A transaction groups SQL statements so they all succeed or all roll back together; savepoints add nested rollback points within a transaction without aborting the whole unit of work.

  • ✓BEGIN / COMMIT / ROLLBACK form the boundaries of a transaction; without BEGIN, most databases auto-commit each statement.
  • ✓SAVEPOINT creates a named sub-checkpoint; ROLLBACK TO SAVEPOINT undoes to that point without aborting the outer transaction.
  • ✓RELEASE SAVEPOINT drops the checkpoint; it does not commit or roll back — it just removes the save point.
  • ✓Deadlocks are detected automatically by the database; the victim transaction receives an error and must retry.
  • ✓Prevent deadlocks by always acquiring locks in a consistent order and keeping transactions short.
  • ✓SERIALIZABLE isolation prevents all read anomalies but increases lock contention — use only when required for correctness.
SQL — transaction with SAVEPOINT for bank transfer
-- Bank transfer: debit user 1, credit user 2
BEGIN;

  -- Save a checkpoint after validation
  SAVEPOINT before_transfer;

  -- Debit sender (using orders table as a balance proxy here)
  UPDATE users SET balance = balance - 500 WHERE id = 1;
  -- Check sufficient funds
  -- (In a real system you'd SELECT and check in application code)

  SAVEPOINT after_debit;

  -- Credit receiver
  UPDATE users SET balance = balance + 500 WHERE id = 2;

  -- If credit UPDATE failed (e.g. user 2 doesn't exist), roll back only the credit:
  -- ROLLBACK TO SAVEPOINT after_debit;
  -- Then decide whether to abort the whole transaction:
  -- ROLLBACK;

  -- Insert audit log regardless
  INSERT INTO orders (user_id, amount, status, created_at)
  VALUES (1, 500, 'transfer_debit', NOW());

COMMIT;
-- Either both balance changes are committed or neither is
Learn this free with Aria, your AI tutor → AiCanCode.org/learn/sql