schema.sql

-- =============================================================
-- Section 06 — Foreign Keys
-- =============================================================
-- Three-table e-commerce model:
--   customer  (parent)
--      └── order (child, FK -> customer)
--              └── order_item (grandchild, FK -> order)
--   product   (parent, referenced from order_item)
-- =============================================================

DROP SCHEMA IF EXISTS adv_fks CASCADE;
CREATE SCHEMA adv_fks;
SET search_path TO adv_fks;

CREATE TABLE customer (
    id        SERIAL PRIMARY KEY,
    full_name VARCHAR(200) NOT NULL,
    email     VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE product (
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(200) NOT NULL,
    price_cents INTEGER      NOT NULL CHECK (price_cents >= 0)
);

CREATE TABLE "order" (
    id          SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    placed_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    total_cents INTEGER NOT NULL DEFAULT 0,

    CONSTRAINT order_customer_fk
        FOREIGN KEY (customer_id)
        REFERENCES  customer (id)
        ON DELETE CASCADE
);

CREATE TABLE order_item (
    id         SERIAL PRIMARY KEY,
    order_id   INTEGER NOT NULL,
    product_id INTEGER,         -- nullable on purpose for SET NULL demo
    quantity   INTEGER NOT NULL CHECK (quantity > 0),

    CONSTRAINT order_item_order_fk
        FOREIGN KEY (order_id)
        REFERENCES  "order" (id)
        ON DELETE CASCADE,

    CONSTRAINT order_item_product_fk
        FOREIGN KEY (product_id)
        REFERENCES  product (id)
        ON DELETE SET NULL
);