What is the difference between a surrogate key, a natural key, a candidate key, and an alternate key, and why do production schemas usually prefer a surrogate key?

-- The accounts table has three candidate keys
CREATE TABLE accounts (
  account_id   VARCHAR(20) PRIMARY KEY,   -- candidate 1 (surrogate)
  pan          VARCHAR(10) UNIQUE,         -- candidate 2 (natural)
  email        VARCHAR(100) UNIQUE,        -- candidate 3 (natural)
  holder_name  VARCHAR(100),
  balance      NUMERIC(15,2)
);

-- Each of account_id, pan, and email can uniquely identify a row

-- Using PAN as a natural primary key (common but problematic)
CREATE TABLE customers_natural (
  pan          VARCHAR(10) PRIMARY KEY,  -- natural key: real-world meaning
  full_name    VARCHAR(100),
  branch       VARCHAR(50)
);

-- Every child table must reference PAN, spreading sensitive data
CREATE TABLE loans_natural (
  loan_id      SERIAL PRIMARY KEY,
  pan          VARCHAR(10) REFERENCES customers_natural(pan),  -- PAN leaked!
  amount       NUMERIC(15,2),
  status       VARCHAR(20)
);

-- Using a surrogate primary key (preferred in production)
CREATE TABLE customers_surrogate (
  customer_id  SERIAL PRIMARY KEY,         -- surrogate: no meaning
  pan          VARCHAR(10) UNIQUE,          -- alternate key (natural)
  email        VARCHAR(100) UNIQUE,         -- alternate key (natural)
  full_name    VARCHAR(100),
  branch       VARCHAR(50)
);

-- Child tables reference the surrogate, not the PAN
CREATE TABLE loans_surrogate (
  loan_id      SERIAL PRIMARY KEY,
  customer_id  INTEGER REFERENCES customers_surrogate(customer_id),
  amount       NUMERIC(15,2),
  status       VARCHAR(20)
);

-- customer_id is primary, pan and email are alternate keys
ALTER TABLE customers_surrogate ADD CONSTRAINT uq_pan UNIQUE (pan);
ALTER TABLE customers_surrogate ADD CONSTRAINT uq_email UNIQUE (email);

package com.bank.model;

import java.math.BigDecimal;

public class Account {
    // Surrogate key: meaningless, stable, never exposed in URLs
    private final long accountId;
    // Natural keys kept for business logic but not as identity
    private final String pan;
    private final String email;
    private final String holderName;
    private BigDecimal balance;

    public Account(long accountId, String pan, String email,
                   String holderName, BigDecimal balance) {
        this.accountId = accountId;
        this.pan = pan;
        this.email = email;
        this.holderName = holderName;
        this.balance = balance;
    }

    public long getAccountId() { return accountId; }
    public String getPan() { return pan; }
    public String getEmail() { return email; }
}

-- Problem: PAN reissued by government, customer has loans, transactions, cards
-- With natural key as PK, all child tables must cascade
UPDATE customers_natural SET pan = 'NEWPN9876Z' WHERE pan = 'ABCDE1234F';
-- Must cascade to: loans, transactions, cards, audit_log, ...
-- Millions of rows updated across many tables

-- With surrogate key as PK, only ONE column in ONE row changes
UPDATE customers_surrogate SET pan = 'NEWPN9876Z' WHERE customer_id = 42;
-- No cascade needed. All child tables reference customer_id, not pan.

-- WRONG: surrogate key but no unique constraint on natural key
CREATE TABLE customers_bad (
  customer_id  SERIAL PRIMARY KEY,
  pan          VARCHAR(10),       -- no UNIQUE constraint!
  full_name    VARCHAR(100)
);

INSERT INTO customers_bad (pan, full_name) VALUES ('ABCDE1234F', 'Rajesh Kumar');
INSERT INTO customers_bad (pan, full_name) VALUES ('ABCDE1234F', 'Rajesh K.');
-- Both succeed! Two rows with the same PAN. Logical duplicate.

-- CORRECT: surrogate key PLUS unique constraint on natural key
CREATE TABLE customers_good (
  customer_id  SERIAL PRIMARY KEY,
  pan          VARCHAR(10) UNIQUE,  -- prevents duplicates
  full_name    VARCHAR(100)
);

-- Auto-increment: sequential, compact, good index locality
CREATE TABLE accounts_serial (
  id SERIAL PRIMARY KEY,
  holder_name VARCHAR(100)
);

-- UUID: globally unique, no coordination, but random scatter
CREATE TABLE accounts_uuid (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  holder_name VARCHAR(100)
);

-- UUIDv7 (time-ordered): best of both worlds
-- Requires PostgreSQL extension or application-generated values