What is the difference between a primary key and an index

-- National Bank of India: account_id is the primary key
CREATE TABLE accounts (
    account_id   VARCHAR(20) PRIMARY KEY,  -- unique + not null + one per table
    customer_name VARCHAR(100) NOT NULL,
    pan          VARCHAR(10) NOT NULL,
    branch_code  VARCHAR(10) NOT NULL,
    balance      DECIMAL(15,2) NOT NULL DEFAULT 0.00
);

-- The database enforces:
-- 1. No two rows can have the same account_id
-- 2. account_id can never be NULL
-- 3. This is the ONLY primary key on this table

-- This fails: duplicate primary key
INSERT INTO accounts VALUES ('SAV2001', 'Rajesh', 'ABC', 'MUM001', 50000);
INSERT INTO accounts VALUES ('SAV2001', 'Priya', 'DEF', 'DEL001', 30000);
-- ERROR: duplicate key value violates unique constraint

-- This fails: null primary key
INSERT INTO accounts VALUES (NULL, 'Amit', 'GHI', 'BLR001', 20000);
-- ERROR: null value in column "account_id" violates not-null constraint

-- Plain index on branch_code: allows duplicates, allows nulls
CREATE INDEX ix_accounts_branch ON accounts(branch_code);

-- Many accounts share branch 'MUM001' – no rule violated
SELECT * FROM accounts WHERE branch_code = 'MUM001';
-- Returns 5000 rows, all with the same branch_code

-- You can create many indexes on one table
CREATE INDEX ix_accounts_name ON accounts(customer_name);
CREATE INDEX ix_accounts_balance ON accounts(balance);
-- Three indexes on the same table, none enforcing any rule

-- The primary key creates an index automatically
-- You can see it in the system catalog
SELECT indexname, indexdef
FROM   pg_indexes
WHERE  tablename = 'accounts';

indexname      | indexdef
---------------+----------------------------------------------
accounts_pkey  | CREATE UNIQUE INDEX accounts_pkey ON accounts
               |   USING btree (account_id)
ix_accounts_branch | CREATE INDEX ix_accounts_branch ON accounts
               |   USING btree (branch_code)

-- transactions.account_id references accounts.account_id (the PK)
CREATE TABLE transactions (
    txn_id     BIGSERIAL PRIMARY KEY,
    account_id VARCHAR(20) NOT NULL
        REFERENCES accounts(account_id),  -- references the PK
    amount     DECIMAL(15,2) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- This succeeds: account SAV2001 exists
INSERT INTO transactions (account_id, amount)
VALUES ('SAV2001', 15000.00);

-- This fails: account SAV9999 does not exist
INSERT INTO transactions (account_id, amount)
VALUES ('SAV9999', 5000.00);
-- ERROR: insert or update on table "transactions" violates
-- foreign key constraint

-- Unique index on PAN: enforces uniqueness, not the row's identity
CREATE UNIQUE INDEX ux_accounts_pan ON accounts(pan);

-- Unique index on email: enforces uniqueness, not the row's identity
CREATE UNIQUE INDEX ux_accounts_email ON accounts(email);

-- The PK is still account_id – that is what FKs reference
-- PAN and email are secondary natural keys protected by unique indexes

-- InnoDB: primary key IS the clustered index
-- Leaf pages of the PK B-tree contain the actual row data
-- Every secondary index stores the PK as its row pointer

-- PostgreSQL: primary key is a UNIQUE NOT NULL index over a heap
-- Rows are stored in a heap (no physical order)
-- The PK index points to the heap location (ctid)

// com.bank.service.AccountService
package com.bank.service;

import java.math.BigDecimal;
import java.sql.*;

public class AccountService {

    /**
     * PK lookup: fastest path, uses the clustered index.
     * Constraint guarantees exactly 0 or 1 row.
     */
    public BigDecimal getBalanceByPK(Connection conn, String accountId)
            throws SQLException {
        String sql = "SELECT balance FROM accounts WHERE account_id = ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, accountId);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) return rs.getBigDecimal("balance");
                return null;
            }
        }
    }

    /**
     * Index lookup: uses the branch_code index.
     * No uniqueness guarantee – may return many rows.
     */
    public int countByBranch(Connection conn, String branchCode)
            throws SQLException {
        String sql = "SELECT COUNT(*) FROM accounts WHERE branch_code = ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, branchCode);
            try (ResultSet rs = ps.executeQuery()) {
                rs.next();
                return rs.getInt(1);
            }
        }
    }
}

-- Wrong thinking: "I have an index on email, so email is a primary key"
-- No! The index enforces no rule unless it is UNIQUE

-- Another mistake: making a wide natural key the PK
-- Bad: email as PK (long string, changes over time)
CREATE TABLE accounts_bad (
    email VARCHAR(255) PRIMARY KEY  -- wide, mutable
);

-- Good: compact surrogate PK + unique index on natural key
CREATE TABLE accounts_good (
    account_id VARCHAR(20) PRIMARY KEY,  -- compact, immutable
    email      VARCHAR(255) NOT NULL
);
CREATE UNIQUE INDEX ux_accounts_email ON accounts_good(email);