Constraints

-- 1) Column constraints — inline
CREATE TABLE users (
    id          BIGSERIAL PRIMARY KEY,
    email       TEXT NOT NULL UNIQUE,
    age         INT CHECK (age >= 0),
    role        TEXT NOT NULL CHECK (role IN ('admin', 'user', 'guest')) DEFAULT 'user',
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- 2) Table-level constraints — named
CREATE TABLE products (
    id           BIGSERIAL PRIMARY KEY,
    sku          TEXT NOT NULL,
    price_cents  BIGINT NOT NULL,
    discount     NUMERIC(5,2),
    CONSTRAINT products_sku_unique     UNIQUE (sku),
    CONSTRAINT products_price_positive CHECK (price_cents > 0),
    CONSTRAINT products_discount_range CHECK (discount IS NULL OR (discount >= 0 AND discount <= 1))
);

-- Always name your constraints. Default names are auto-generated and ugly.

-- 3) Foreign keys
CREATE TABLE orders (
    id          BIGSERIAL PRIMARY KEY,
    user_id     BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    product_id  BIGINT NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
    quantity    INT NOT NULL CHECK (quantity > 0)
);

-- Actions:
--   ON DELETE NO ACTION     default; refuses deletion if rows reference (deferrable)
--   ON DELETE RESTRICT      same as NO ACTION but cannot be deferred
--   ON DELETE CASCADE       delete referencing rows
--   ON DELETE SET NULL      null out the FK
--   ON DELETE SET DEFAULT   use the column DEFAULT

-- 4) Composite primary key
CREATE TABLE user_roles (
    user_id  BIGINT REFERENCES users(id) ON DELETE CASCADE,
    role     TEXT NOT NULL,
    PRIMARY KEY (user_id, role)
);

-- 5) UNIQUE constraint vs UNIQUE index
CREATE TABLE invitations (
    id       BIGSERIAL PRIMARY KEY,
    email    TEXT NOT NULL,
    accepted BOOLEAN NOT NULL DEFAULT false
);

-- Conditional uniqueness — only ONE outstanding invite per email
CREATE UNIQUE INDEX invitations_one_open_per_email
    ON invitations (email) WHERE accepted = false;

-- 6) Multi-column CHECK
CREATE TABLE bookings (
    id          BIGSERIAL PRIMARY KEY,
    start_at    TIMESTAMPTZ NOT NULL,
    end_at      TIMESTAMPTZ NOT NULL,
    CHECK (end_at > start_at)
);

-- 7) Exclusion constraints — like UNIQUE but with arbitrary operators
CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE bookings2 (
    id           BIGSERIAL PRIMARY KEY,
    room_id      BIGINT NOT NULL,
    period       TSTZRANGE NOT NULL,
    EXCLUDE USING GIST (room_id WITH =, period WITH &&)
    -- no two rows for the SAME room with OVERLAPPING time ranges
);

INSERT INTO bookings2 (room_id, period)
VALUES (1, tstzrange('2025-01-01 09:00+00', '2025-01-01 10:00+00'));
-- Second insert that overlaps fails:
INSERT INTO bookings2 (room_id, period)
VALUES (1, tstzrange('2025-01-01 09:30+00', '2025-01-01 11:00+00'));
-- ERROR: conflicting key value violates exclusion constraint

-- 8) Deferrable constraints — defer to end of transaction
CREATE TABLE accounts (
    id        BIGSERIAL PRIMARY KEY,
    balance   BIGINT NOT NULL,
    CONSTRAINT positive_balance CHECK (balance >= 0) DEFERRABLE INITIALLY DEFERRED
);

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- temporarily negative OK
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- both checks happen here

-- 9) Domains — reusable typed constraints
CREATE DOMAIN email_address AS TEXT
    CHECK (VALUE ~ '^[^@\s]+@[^@\s]+\.[^@\s]+$');

CREATE DOMAIN positive_money AS BIGINT
    CHECK (VALUE > 0);

CREATE TABLE customers (
    id     BIGSERIAL PRIMARY KEY,
    email  email_address NOT NULL,
    spend  positive_money
);

-- 10) Generated columns
CREATE TABLE orders2 (
    id              BIGSERIAL PRIMARY KEY,
    subtotal_cents  BIGINT NOT NULL,
    tax_cents       BIGINT NOT NULL GENERATED ALWAYS AS (subtotal_cents / 10) STORED,
    total_cents     BIGINT NOT NULL GENERATED ALWAYS AS (subtotal_cents + subtotal_cents / 10) STORED
);

-- 11) Adding constraints to existing tables
ALTER TABLE products ADD CONSTRAINT products_sku_format CHECK (sku ~ '^[A-Z0-9-]+$');
ALTER TABLE products ADD CONSTRAINT products_price_positive CHECK (price_cents > 0);
ALTER TABLE products ADD CONSTRAINT fk_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id);
ALTER TABLE products DROP CONSTRAINT products_sku_format;

-- For huge tables, validate in two steps:
ALTER TABLE products ADD CONSTRAINT chk_price CHECK (price_cents > 0) NOT VALID;
-- ... let it validate without locking the table
ALTER TABLE products VALIDATE CONSTRAINT chk_price;

-- 12) Constraint introspection
SELECT conname, contype, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'products'::regclass;

-- contype: p=primary, u=unique, c=check, f=foreign, x=exclusion, t=trigger

-- 13) Common patterns
--   • Soft delete: deleted_at TIMESTAMPTZ; partial unique 'WHERE deleted_at IS NULL'
--   • Status fields: CHECK on enum-like text values
--   • Money: BIGINT cents + CHECK (>= 0)
--   • Optional FK: column NULL allowed, FK still enforced when present
--   • Cascading deletes only where ownership is clear (orders → order_lines: yes; users → orders: think twice)

-- 14) Performance + correctness trade-offs
--   • Foreign keys with indexes on both sides — joins fast; deletes also fast
--   • CHECK constraints are evaluated on every insert/update
--   • Exclusion constraints use GIST indexes — slower than B-tree but correct
--   • Deferred constraints help complex multi-row updates within a transaction
--   • UNIQUE indexes are HOT-pages on inserts — partitioning helps at extreme scale

-- 15) Common bugs
--   • Missing NOT NULL on FK columns → orphan-looking NULLs
--   • CHECK with NULL — three-valued logic; 'x = 1' is NULL when x is NULL (constraint passes!)
--     Use IS NULL / IS NOT NULL explicitly
--   • Forgot ON DELETE CASCADE → deletion blocked or orphans created
--   • CHECK referencing another table → not supported; use a trigger
--   • Renaming a constraint without updating migrations → drift between schema + DBs
--   • Long ALTER TABLE … ADD CONSTRAINT → table lock; use NOT VALID + VALIDATE
--   • Multiple users hitting a unique constraint → handle the unique-violation error gracefully
--   • Domain changes affect every column using it — communicate before altering
--   • Deferrable constraint not actually deferred — SET CONSTRAINTS ALL DEFERRED inside the txn

CREATE TABLE orders (
    id bigserial PRIMARY KEY,
    user_id bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    total numeric(10,2) CHECK (total >= 0),
    UNIQUE (user_id, id)
);