Lesson 10 of 12

Schema Design & Constraints

Constraints are rules the enforces on every write, whatever program makes it. They keep bad data out even when application code has or someone edits the table by hand.

SQL

CREATE TABLE customers (
    id          BIGSERIAL PRIMARY KEY,
    email       TEXT        NOT NULL UNIQUE,
    name        TEXT        NOT NULL,
    country     CHAR(2)     NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT email_format CHECK (email LIKE '%_@_%.__%')
);

CREATE TABLE orders (
    id           BIGSERIAL PRIMARY KEY,
    customer_id  BIGINT      NOT NULL,
    status       TEXT        NOT NULL DEFAULT 'pending',
    total        NUMERIC(12,2) NOT NULL,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT fk_customer
        FOREIGN KEY (customer_id) REFERENCES customers(id)
        ON DELETE CASCADE,          -- delete the customer, delete their orders
                                    -- alternatives: RESTRICT, SET NULL, NO ACTION
    CONSTRAINT positive_total CHECK (total >= 0),
    CONSTRAINT valid_status  CHECK (status IN ('pending','paid','shipped','cancelled'))
);

-- Many-to-many needs a join table with a composite primary key
CREATE TABLE order_items (
    order_id   BIGINT NOT NULL REFERENCES orders(id)   ON DELETE CASCADE,
    product_id BIGINT NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
    quantity   INT    NOT NULL CHECK (quantity > 0),
    unit_price NUMERIC(12,2) NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

-- Normalization: store each fact exactly once.

-- BAD: repeated data, no single source of truth
-- orders(id, customer_name, customer_email, customer_country, product_name, price)
--   * changing an email means updating every one of that customer's orders
--   * two rows can disagree about the same customer
--   * a customer with no orders cannot exist at all

-- GOOD: one table per entity, linked by keys
-- customers(id, name, email, country)
-- products(id, name, price)
-- orders(id, customer_id, created_at)
-- order_items(order_id, product_id, quantity, unit_price)

-- Note that order_items stores unit_price even though products has a price.
-- That is deliberate, not duplication: an order must remember what was charged
-- AT THE TIME, even after the product price changes.

-- Deliberate denormalization for read speed — keep it explicit and derived:
ALTER TABLE orders ADD COLUMN item_count INT NOT NULL DEFAULT 0;
-- ... maintained by a trigger or by the application, and rebuildable from
-- order_items at any time.

-- Normalization: store each fact exactly once.

-- BAD: repeated data, no single source of truth
-- orders(id, customer_name, customer_email, customer_country, product_name, price)
--   * changing an email means updating every one of that customer's orders
--   * two rows can disagree about the same customer
--   * a customer with no orders cannot exist at all

-- GOOD: one table per entity, linked by keys
-- customers(id, name, email, country)
-- products(id, name, price)
-- orders(id, customer_id, created_at)
-- order_items(order_id, product_id, quantity, unit_price)

-- Note that order_items stores unit_price even though products has a price.
-- That is deliberate, not duplication: an order must remember what was charged
-- AT THE TIME, even after the product price changes.

-- Deliberate denormalization for read speed — keep it explicit and derived:
ALTER TABLE orders ADD COLUMN item_count INT NOT NULL DEFAULT 0;
-- ... maintained by a trigger or by the application, and rebuildable from
-- order_items at any time.

SQL

-- Changing a live schema: additive first, destructive later.
ALTER TABLE customers ADD COLUMN phone TEXT;                    -- safe
ALTER TABLE customers ALTER COLUMN name SET NOT NULL;           -- needs a backfill first
ALTER TABLE customers RENAME COLUMN country TO country_code;    -- breaks old code
ALTER TABLE customers DROP COLUMN phone;                        -- irreversible

-- Add a constraint without locking the table for hours (Postgres)
ALTER TABLE orders ADD CONSTRAINT total_sane CHECK (total < 1000000) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT total_sane;

-- Useful column types
--   BIGSERIAL / IDENTITY   auto-incrementing keys
--   UUID                   keys generated by the client
--   NUMERIC(12,2)          money. NEVER use FLOAT for money.
--   TIMESTAMPTZ            an instant. Store UTC, format on display.
--   TEXT                   strings (no reason to guess a VARCHAR length)
--   JSONB                  semi-structured data you also want to query
--   BOOLEAN                a flag; NOT NULL DEFAULT FALSE

-- Soft deletes keep history but every query must now filter
ALTER TABLE customers ADD COLUMN deleted_at TIMESTAMPTZ;
CREATE INDEX idx_customers_active ON customers(id) WHERE deleted_at IS NULL;

Finished reading?Mark it done to track your progress.

Tip: use the ← and → keys to move between lessons.