Primary, Foreign, and Unique Keys

1. Introduction

Keys are the foundation of relational database integrity. A Primary Key uniquely identifies each row. A Foreign Key enforces referential integrity between tables. A Unique Key prevents duplicate values in a column. Together, they form the integrity backbone that prevents orphaned records, duplicate entries, and broken relationships.

2. Why It Matters

• Data integrity: Keys prevent duplicate users, orphaned orders, and dangling references — the most common source of production data bugs.
• Query performance: Primary keys automatically create unique B-tree indexes, making lookups by ID extremely fast.
• Cascade behavior: Foreign keys with ON DELETE CASCADE automatically clean up child records, preventing orphaned data.
• Schema documentation: Keys serve as self-documenting constraints — any developer can understand table relationships by reading the schema.

3. Real-World Analogy

4. How It Works

-- PRIMARY KEY: Uniquely identifies each row
CREATE TABLE users (
    id SERIAL PRIMARY KEY,  -- auto-incrementing integer
    email TEXT NOT NULL,
    name TEXT NOT NULL
);
-- Composite primary key
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT NOT NULL,
    PRIMARY KEY (order_id, product_id)
);
-- FOREIGN KEY: Enforces referential integrity
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL REFERENCES users(id)
        ON DELETE CASCADE,    -- delete orders when user deleted
        ON UPDATE CASCADE,    -- update order user_id if user id changes
    created_at TIMESTAMP DEFAULT NOW()
);
-- UNIQUE KEY: Prevents duplicates without being primary key
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email TEXT UNIQUE NOT NULL,    -- unique constraint
    username TEXT,
    CONSTRAINT unique_username UNIQUE (username)
);
-- Multi-column unique constraint
CREATE TABLE subscriptions (
    user_id INT REFERENCES users(id),
    plan_id INT REFERENCES plans(id),
    UNIQUE (user_id, plan_id)  -- one subscription per user per plan
);

5. Internal Architecture

PostgreSQL Key Internals:
PRIMARY KEY:
  → Automatically creates a UNIQUE B-tree index
  → Automatically adds NOT NULL constraint
  → Index name: {table}_{column}_pkey
  → Clustered by default (physical row order matches PK)
FOREIGN KEY:
  → Creates a trigger-based constraint check
  → On INSERT/UPDATE of child: checks parent exists
  → On DELETE/UPDATE of parent: checks FK action
  → Actions: CASCADE, SET NULL, SET DEFAULT, RESTRICT, NO ACTION
  → No automatic index on FK column (add manually for performance!)
UNIQUE KEY:
  → Creates a UNIQUE B-tree index
  → Allows multiple NULL values (NULL != NULL)
  → Can be used as conflict target for ON CONFLICT
Performance tip: Always create an index on FK columns!
  CREATE INDEX idx_orders_user_id ON orders(user_id);
  Without it, DELETE on parent table requires a seq scan of child.

6. Visual Explanation

7. Practical Example

-- Complete e-commerce schema with keys
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name TEXT UNIQUE NOT NULL
);
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    category_id INT NOT NULL REFERENCES categories(id)
        ON DELETE RESTRICT,  -- can't delete category with products
    name TEXT NOT NULL,
    sku TEXT UNIQUE NOT NULL,
    price NUMERIC(10,2) NOT NULL CHECK (price >= 0)
);
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email TEXT UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL REFERENCES users(id)
        ON DELETE CASCADE,
    status TEXT DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT NOW()
);
CREATE TABLE order_items (
    order_id INT REFERENCES orders(id) ON DELETE CASCADE,
    product_id INT REFERENCES products(id) ON DELETE RESTRICT,
    quantity INT NOT NULL CHECK (quantity > 0),
    unit_price NUMERIC(10,2) NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

8. Common Mistakes

9. Quick Quiz

10. Scenario-Based Challenge

11. Debugging Exercise

-- Delete a category that has products
DELETE FROM categories WHERE id = 5;
-- The categories table has ON DELETE RESTRICT on products.category_id
-- products table has 10 million rows

12. Interview Questions

13. Production Considerations

• Always index FK columns: Run this query to find unindexed FK columns: SELECT ... FROM information_schema.key_column_usage WHERE ... AND NOT EXISTS (SELECT 1 FROM pg_indexes ...)
• Use RESTRICT for safety: Default to ON DELETE RESTRICT for important relationships. Use CASCADE only when child records have no meaning without the parent.
• DEFERRABLE constraints: For complex transactions that temporarily violate FKs, use DEFERRABLE INITIALLY DEFERRED to check at COMMIT time.
• Disabling FKs during bulk loads: For initial data migration, you can SET session_replication_role = 'replica' to skip FK checks, then re-enable. Use with extreme caution.
• Monitoring: Track constraint violation errors in application logs. A spike usually indicates a bug in data creation logic.