FOREIGN KEY

-- Foreign key definition
CREATE TABLE orders (
  id         SERIAL PRIMARY KEY,
  user_id    INT NOT NULL REFERENCES users(id),     -- shorthand
  product_id INT REFERENCES products(id) ON DELETE SET NULL,
  ...
);

-- Full syntax
CREATE TABLE orders (
  id      SERIAL PRIMARY KEY,
  user_id INT NOT NULL,
  CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE RESTRICT    -- default: prevent deletion of referenced row
    ON UPDATE CASCADE     -- update FK automatically if PK changes
);

-- ON DELETE actions:
-- RESTRICT:  prevent deletion (error if referenced rows exist)
-- CASCADE:   delete child rows when parent is deleted
-- SET NULL:  set FK to NULL when parent is deleted
-- SET DEFAULT: set FK to default value when parent is deleted
-- NO ACTION: same as RESTRICT (checked at end of transaction)

-- Add FK to existing table
ALTER TABLE orders ADD CONSTRAINT fk_user
  FOREIGN KEY (user_id) REFERENCES users(id);

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

CREATE TABLE orders (
  id       SERIAL PRIMARY KEY,
  user_id  INT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
  -- RESTRICT: can't delete a user who has orders (protect history)
  status   VARCHAR(20) DEFAULT 'pending'
);

CREATE TABLE order_items (
  id         SERIAL PRIMARY KEY,
  order_id   INT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  -- CASCADE: deleting an order deletes its items too (makes sense!)
  product_id INT NOT NULL REFERENCES products(id) ON DELETE RESTRICT
  -- RESTRICT: can't delete a product that has been ordered
);

-- Index FK columns! (critical for join performance)
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);