Foreign Keys + ON DELETE Behavior

-- CASCADE: deleting a user also deletes their orders
CREATE TABLE orders (
    id      BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    amount  NUMERIC NOT NULL
);

-- SET NULL: deleting a user leaves orders intact but unowned
CREATE TABLE comments (
    id         BIGSERIAL PRIMARY KEY,
    author_id  BIGINT REFERENCES users(id) ON DELETE SET NULL,
    body       TEXT NOT NULL
);

-- RESTRICT (default): refuses to delete the parent if children exist
CREATE TABLE invoices (
    id        BIGSERIAL PRIMARY KEY,
    order_id  BIGINT NOT NULL REFERENCES orders(id) ON DELETE RESTRICT
);

-- Add an FK to an existing table
ALTER TABLE orders
    ADD CONSTRAINT fk_orders_user
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

-- ⚠️ ALWAYS add an INDEX on the FK column —
-- DBs don\'t auto-index FKs (except MySQL InnoDB), so a parent DELETE
-- can scan the whole child table to find references.
CREATE INDEX ON orders (user_id);