Effective Use of PRIMARY KEY in SQL

1. Surrogate Keys

Surrogate keys are artificial identifiers that have no business meaning. They exist solely to uniquely identify each row.

Auto-incrementing Integers

-- MySQL
CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL
);

-- SQL Server
CREATE TABLE products (
    product_id INT IDENTITY(1,1) PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL
);

-- PostgreSQL
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL
);

Auto-incrementing integers are simple, space-efficient, and perform well for most applications. They're ideal for tables with frequent inserts and lookups.

UUIDs/GUIDs

-- PostgreSQL
CREATE TABLE sessions (
    session_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id INT NOT NULL,
    login_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_activity TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- SQL Server
CREATE TABLE sessions (
    session_id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    user_id INT NOT NULL,
    login_time DATETIME NOT NULL DEFAULT GETDATE(),
    last_activity DATETIME NOT NULL DEFAULT GETDATE()
);

UUIDs (Universally Unique Identifiers) are 128-bit values that are globally unique. They're excellent for distributed systems, data synchronization, and scenarios where IDs need to be generated outside the database.

2. Natural Keys

Natural keys use existing business data that uniquely identifies each row. Examples include email addresses, social security numbers, or product codes.

CREATE TABLE employees (
    employee_id VARCHAR(20) PRIMARY KEY, -- Company-assigned employee ID
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE countries (
    country_code CHAR(2) PRIMARY KEY, -- ISO country code
    country_name VARCHAR(100) NOT NULL,
    population BIGINT
);

3. Composite Keys

Composite keys use multiple columns together to form a unique identifier. They're useful when no single column can uniquely identify a row.

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    semester VARCHAR(20),
    grade CHAR(1),
    PRIMARY KEY (student_id, course_id, semester)
);

CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

Composite keys are common in junction tables that represent many-to-many relationships. They ensure that the same relationship isn't recorded multiple times.

Performance Optimization

PRIMARY KEYs significantly impact database performance. Here are some advanced considerations:

• Clustered vs. Non-clustered Indexes: In SQL Server, PRIMARY KEYs create clustered indexes by default, which physically orders the data. This can improve range queries but may impact insert performance in high-write scenarios.
• Fill Factor: For large tables with frequent updates, consider adjusting the fill factor of PRIMARY KEY indexes to reduce page splits and fragmentation.
• Key Size: Smaller PRIMARY KEYs (like integers) require less storage in both the table and in any FOREIGN KEY references, improving overall performance.

-- SQL Server: Creating a non-clustered primary key
CREATE TABLE large_logging_table (
    log_id BIGINT IDENTITY(1,1),
    log_time DATETIME2 NOT NULL,
    message NVARCHAR(MAX),
    CONSTRAINT PK_large_logging_table PRIMARY KEY NONCLUSTERED (log_id)
);

-- Creating a clustered index on the timestamp for time-based queries
CREATE CLUSTERED INDEX IX_large_logging_table_log_time ON large_logging_table (log_time);

Sharding and Distributed Systems

In distributed database systems, PRIMARY KEY design becomes even more critical:

• Sharding Keys: PRIMARY KEYs often serve as sharding keys to distribute data across multiple database instances.
• Globally Unique IDs: Consider algorithms like Twitter's Snowflake or Instagram's UUID generation that create globally unique IDs across distributed systems.
• Composite Sharding Strategy: Sometimes a combination of time-based and entity-based sharding works best, requiring carefully designed composite PRIMARY KEYs.

-- Example of a table designed for time-based sharding
CREATE TABLE user_events (
    -- First part of key determines the shard (month)
    event_month DATE,
    -- Second part ensures uniqueness within the shard
    event_id BIGINT,
    user_id BIGINT NOT NULL,
    event_type VARCHAR(50) NOT NULL,
    event_data JSONB,
    created_at TIMESTAMP NOT NULL,
    PRIMARY KEY (event_month, event_id)
);

Database-Specific Implementations

Different database systems have unique features for PRIMARY KEYs:

PostgreSQL

• Offers SERIAL, BIGSERIAL, and UUID generation functions
• Supports GENERATED ALWAYS AS IDENTITY for standards-compliant auto-incrementing columns
• Provides extensions like "pgcrypto" for advanced UUID generation

MySQL/MariaDB

• AUTO_INCREMENT is the standard for generating sequential IDs
• InnoDB engine uses clustered indexes on PRIMARY KEYs by default
• Supports UUID() function but stores UUIDs as CHAR(36) by default

SQL Server

• IDENTITY property for auto-incrementing columns
• SEQUENCE objects for more flexible ID generation
• UNIQUEIDENTIFIER type with NEWID() and NEWSEQUENTIALID() functions

Oracle

• IDENTITY columns (in 12c and later)
• SEQUENCE objects with more control over ID generation
• RAW(16) type for efficient UUID storage

Altering PRIMARY KEYs

Sometimes you need to modify PRIMARY KEYs on existing tables. Here's how to do it safely:

-- PostgreSQL: Adding a PRIMARY KEY to an existing table
ALTER TABLE products ADD PRIMARY KEY (product_id);

-- Removing a PRIMARY KEY
ALTER TABLE products DROP CONSTRAINT products_pkey;

-- Changing a PRIMARY KEY (two-step process)
ALTER TABLE orders DROP CONSTRAINT orders_pkey;
ALTER TABLE orders ADD PRIMARY KEY (new_order_id);

-- Adding a composite PRIMARY KEY
ALTER TABLE order_items ADD PRIMARY KEY (order_id, product_id);

Duplicate Key Violations

When you encounter a "duplicate key violation" error, it means you're trying to insert a row with a PRIMARY KEY value that already exists.

-- PostgreSQL: Find duplicate values before creating a PRIMARY KEY
SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > 1;

-- MySQL: Insert with ON DUPLICATE KEY UPDATE to handle duplicates
INSERT INTO products (product_id, product_name, price)
VALUES ('ABC123', 'New Product', 29.99)
ON DUPLICATE KEY UPDATE
product_name = VALUES(product_name),
price = VALUES(price);

Foreign Key Constraints

When you try to delete a row that's referenced by a FOREIGN KEY in another table, you'll get a constraint violation.

-- Find all foreign key references to a specific primary key
-- PostgreSQL
SELECT
    tc.table_schema, 
    tc.table_name, 
    kcu.column_name, 
    ccu.table_schema AS foreign_table_schema,
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name 
FROM 
    information_schema.table_constraints AS tc 
    JOIN information_schema.key_column_usage AS kcu
      ON tc.constraint_name = kcu.constraint_name
      AND tc.table_schema = kcu.table_schema
    JOIN information_schema.constraint_column_usage AS ccu
      ON ccu.constraint_name = tc.constraint_name
      AND ccu.table_schema = tc.table_schema
WHERE tc.constraint_type = 'FOREIGN KEY' 
AND ccu.table_name = 'your_table_name';

Performance Issues

If your PRIMARY KEY is causing performance problems, consider these solutions:

• For SQL Server, consider using a non-clustered PRIMARY KEY if your access patterns don't benefit from clustering.
• For large tables with UUIDs as PRIMARY KEYs, ensure the UUIDs are sequential to reduce index fragmentation.
• Monitor index fragmentation and rebuild indexes regularly on large tables.