-- in the practice database (\c practice)
CREATE SCHEMA IF NOT EXISTS keys;
-- start clean, safe to re-run
DROP TABLE IF EXISTS keys.taxpayers CASCADE;
CREATE TABLE keys.taxpayers (
  -- 1. SURROGATE PRIMARY KEY: the chosen candidate key (synthetic 8-byte anchor)
  taxpayer_id  BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

  -- 2. NATURAL ALTERNATE KEY: real-world business identifier (immutable)
  pan_number   VARCHAR(10) UNIQUE NOT NULL,

  -- 3. SECONDARY ALTERNATE KEY: candidate key enforced via UNIQUE NOT NULL
  email        VARCHAR(150) UNIQUE NOT NULL,

  -- 4. NON-KEY ATTRIBUTE: descriptive domain payload
  full_name    TEXT NOT NULL
);

INSERT INTO keys.taxpayers (pan_number, email, full_name)
VALUES ('ABCDE1234F', '[email protected]', 'Priya Sharma');
SELECT taxpayer_id, pan_number, email, full_name FROM keys.taxpayers LIMIT 10;

-- 💡 The Golden Rule: use surrogate keys for joins and primary keys;
-- enforce natural keys using alternate keys (UNIQUE NOT NULL)!

-- in the practice database (\c practice)
CREATE SCHEMA IF NOT EXISTS keys;
-- 1. Modern single-column surrogate primary key:
-- start clean, safe to re-run
DROP TABLE IF EXISTS keys.shop_order_items,
  keys.shop_orders CASCADE;
CREATE TABLE keys.shop_orders (
  order_id   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_date DATE NOT NULL DEFAULT CURRENT_DATE,
  total      NUMERIC(10,2) NOT NULL CHECK (total >= 0)
);

-- 2. Composite primary key on a junction / line-items table:
CREATE TABLE keys.shop_order_items (
  order_id   BIGINT REFERENCES keys.shop_orders(order_id),
  item_id    INT,
  quantity   INT NOT NULL CHECK (quantity > 0),
  CONSTRAINT pk_shop_order_items PRIMARY KEY (order_id, item_id)
);

INSERT INTO keys.shop_orders (total) VALUES (2499.00);                -- order_id 1 is generated
INSERT INTO keys.shop_order_items VALUES (1, 1, 2), (1, 2, 1);        -- same order, different items: OK
SELECT order_id, item_id, quantity FROM keys.shop_order_items LIMIT 10;
-- Now try (FAILS with Error 23505): the pair (1, 1) already exists
-- INSERT INTO keys.shop_order_items VALUES (1, 1, 5);

-- Step 1: Parent table must exist with a unique identifier
-- in the practice database (\c practice)
CREATE SCHEMA IF NOT EXISTS keys;
-- start clean, safe to re-run
DROP TABLE IF EXISTS keys.orders,
  keys.customers CASCADE;
CREATE TABLE keys.customers (
  cust_id    INT PRIMARY KEY,
  full_name  VARCHAR(100) NOT NULL
);

-- Step 2: Child table enforces referential relationship
CREATE TABLE keys.orders (
  order_id   SERIAL PRIMARY KEY,
  cust_id    INT NOT NULL,
  amount     NUMERIC(10,2) NOT NULL CHECK (amount >= 0),
  CONSTRAINT fk_orders_customer
    FOREIGN KEY (cust_id) REFERENCES keys.customers(cust_id)
);

-- Valid write: customer 101 exists in parent table
INSERT INTO keys.customers (cust_id, full_name) VALUES (101, 'Aarav Sharma');
INSERT INTO keys.orders (cust_id, amount) VALUES (101, 2499.00); -- OK!

-- Blocked write: customer 999 does not exist (Orphan Shield fires)
INSERT INTO keys.orders (cust_id, amount) VALUES (999, 499.00);
-- 💥 ERROR: insert or update on table "orders" violates foreign key constraint "fk_orders_customer"
-- DETAIL: Key (cust_id)=(999) is not present in table "customers".

-- Parents
-- in the practice database (\c practice)
CREATE SCHEMA IF NOT EXISTS keys;
-- start clean, safe to re-run
DROP TABLE IF EXISTS keys.staff,
  keys.comments, keys.departments,
  keys.managers, keys.blog_posts CASCADE;
CREATE TABLE keys.blog_posts  (post_id INT PRIMARY KEY, title VARCHAR(100) NOT NULL);
CREATE TABLE keys.managers    (manager_id INT PRIMARY KEY, name VARCHAR(50) NOT NULL);
CREATE TABLE keys.departments (dept_id INT PRIMARY KEY, name VARCHAR(50) NOT NULL);
-- Children: one ON DELETE rule each
CREATE TABLE keys.comments (comment_id SERIAL PRIMARY KEY,
  post_id INT REFERENCES keys.blog_posts(post_id) ON DELETE CASCADE);   -- disposable: delete them too
CREATE TABLE keys.staff (staff_id SERIAL PRIMARY KEY,
  manager_id INT REFERENCES keys.managers(manager_id) ON DELETE SET NULL, -- reassignable: unlink (must allow NULL)
  dept_id INT NOT NULL REFERENCES keys.departments(dept_id) ON DELETE RESTRICT); -- critical: block it
INSERT INTO keys.blog_posts VALUES (1, 'Intro to SQL');
INSERT INTO keys.comments (post_id) VALUES (1), (1);
INSERT INTO keys.managers VALUES (7, 'Priya');
INSERT INTO keys.departments VALUES (10, 'Tech');
INSERT INTO keys.staff (manager_id, dept_id) VALUES (7, 10);
DELETE FROM keys.blog_posts WHERE post_id = 1;    -- CASCADE: both comments are deleted too
DELETE FROM keys.managers WHERE manager_id = 7;   -- SET NULL: staff row stays, manager_id becomes NULL
SELECT staff_id, manager_id, dept_id FROM keys.staff LIMIT 10;
-- Now try RESTRICT: remove the two dashes below and run again. It is blocked with Error 23503.
-- DELETE FROM keys.departments WHERE dept_id = 10;