BLOG POSTS

MangoHost Blog / Using Primary Keys in SQL

Using Primary Keys in SQL

Primary keys are fundamental building blocks of relational database design that every developer and database administrator needs to understand thoroughly. They serve as unique identifiers for rows in database tables, ensuring data integrity and enabling efficient data retrieval and relationships between tables. In this comprehensive guide, you’ll learn how primary keys work under the hood, best practices for implementation, common pitfalls to avoid, and real-world strategies for optimizing your database performance through proper primary key design.

How Primary Keys Work Under the Hood

A primary key is a column or combination of columns that uniquely identifies each row in a database table. The database engine uses primary keys to create a clustered index by default in most systems, which physically orders the data on disk according to the primary key values. This makes data retrieval incredibly fast when querying by primary key.

Here’s what happens when you define a primary key:

The database automatically creates a unique index on the primary key column(s)
NULL values are prohibited in primary key columns
The primary key constraint ensures no duplicate values can exist
Foreign keys in other tables reference this primary key to establish relationships
Most databases use the primary key for replication and logging operations

The physical storage structure varies by database system, but most modern databases organize data pages around the primary key for optimal performance.

Step-by-Step Implementation Guide

Let’s walk through creating primary keys in different scenarios, starting with the basics and moving to more complex implementations.

Simple Auto-Increment Primary Key

This is the most common approach for new developers:

-- MySQL/MariaDB
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- PostgreSQL
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- SQL Server
CREATE TABLE users (
    id INT IDENTITY(1,1) PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at DATETIME2 DEFAULT GETDATE()
);

Composite Primary Keys

When you need multiple columns to uniquely identify a row:

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),
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

UUID Primary Keys

For distributed systems or when you need globally unique identifiers:

-- PostgreSQL with UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE sessions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id INT NOT NULL,
    token_hash VARCHAR(255) NOT NULL,
    expires_at TIMESTAMP NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- MySQL 8.0+
CREATE TABLE sessions (
    id BINARY(16) PRIMARY KEY DEFAULT (UUID_TO_BIN(UUID())),
    user_id INT NOT NULL,
    token_hash VARCHAR(255) NOT NULL,
    expires_at TIMESTAMP NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

Adding Primary Keys to Existing Tables

Sometimes you inherit tables without proper primary keys:

-- Add auto-increment primary key to existing table
ALTER TABLE legacy_table 
ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY FIRST;

-- Or make existing column primary key
ALTER TABLE legacy_table 
ADD PRIMARY KEY (existing_unique_column);

-- For composite keys
ALTER TABLE legacy_table 
ADD PRIMARY KEY (column1, column2);

Real-World Examples and Use Cases

Let’s examine practical scenarios where different primary key strategies make sense.

E-commerce Platform

In an e-commerce system, you’ll encounter various primary key patterns:

-- Main entities use auto-increment
CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    registration_date DATE
);

-- Junction tables use composite keys
CREATE TABLE customer_addresses (
    customer_id INT,
    address_type ENUM('billing', 'shipping', 'both'),
    street_address VARCHAR(255),
    city VARCHAR(100),
    postal_code VARCHAR(20),
    PRIMARY KEY (customer_id, address_type),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Time-series data with natural composite key
CREATE TABLE price_history (
    product_id INT,
    effective_date DATE,
    price DECIMAL(10,2),
    currency_code CHAR(3),
    PRIMARY KEY (product_id, effective_date),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

Multi-Tenant SaaS Application

For SaaS platforms, you often need tenant-aware primary keys:

CREATE TABLE tenant_users (
    tenant_id INT,
    user_id INT,
    email VARCHAR(255),
    role VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (tenant_id, user_id),
    UNIQUE KEY unique_email_per_tenant (tenant_id, email),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id)
);

High-Performance Analytics

For analytical workloads, consider using natural keys that support efficient partitioning:

CREATE TABLE website_analytics (
    date_key DATE,
    page_url VARCHAR(500),
    visitor_session_id VARCHAR(100),
    page_views INT,
    unique_visitors INT,
    bounce_rate DECIMAL(5,2),
    PRIMARY KEY (date_key, page_url, visitor_session_id)
) PARTITION BY RANGE (YEAR(date_key)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

Performance Comparison: Different Primary Key Types

Here’s how different primary key strategies perform under various conditions:

Primary Key Type	Insert Performance	Storage Overhead	Index Size	Replication Friendly	Distributed Systems
Auto-increment INT	Excellent	4 bytes	Small	Poor	Poor
Auto-increment BIGINT	Excellent	8 bytes	Medium	Poor	Poor
UUID (36 char)	Good	36 bytes	Large	Excellent	Excellent
UUID (16 binary)	Good	16 bytes	Medium-Large	Excellent	Excellent
Composite Key	Variable	Variable	Variable	Good	Good
Natural Key	Variable	Variable	Variable	Good	Fair

Best Practices and Common Pitfalls

Primary Key Selection Guidelines

Keep it simple: Single column primary keys are usually better than composite keys for most applications
Make it immutable: Primary key values should never change once assigned
Avoid meaningful data: Don’t use email addresses, usernames, or other business data as primary keys
Consider growth: Use BIGINT instead of INT if you expect high volume data
Plan for distribution: UUIDs work better for microservices and distributed databases

Performance Optimization Tips

Here are proven strategies for maximizing primary key performance:

-- Bad: Using VARCHAR primary key
CREATE TABLE bad_example (
    email VARCHAR(255) PRIMARY KEY,  -- Avoid this
    user_data JSON
);

-- Good: Separate surrogate key
CREATE TABLE good_example (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    user_data JSON,
    INDEX idx_email (email)  -- Fast lookups on business key
);

Common Mistakes to Avoid

Using NULL-able columns: Primary keys cannot contain NULL values
Choosing overly wide keys: Large primary keys slow down foreign key joins
Frequent key updates: Updating primary keys cascades to all related tables
No primary key at all: Some databases allow this but it breaks replication
Composite keys without thought: Consider if a surrogate key would be simpler

Troubleshooting Common Issues

When primary key operations fail, check these common causes:

-- Duplicate key error
INSERT INTO users (id, username) VALUES (1, 'john');
-- ERROR: Duplicate entry '1' for key 'PRIMARY'

-- Solution: Use auto-increment or check existing values
INSERT INTO users (username) VALUES ('john');

-- Or explicitly handle duplicates
INSERT IGNORE INTO users (id, username) VALUES (1, 'john');
-- MySQL: Ignores duplicate
-- PostgreSQL: Use ON CONFLICT DO NOTHING

For constraint violations:

-- Check existing primary key constraints
SELECT 
    CONSTRAINT_NAME,
    COLUMN_NAME,
    ORDINAL_POSITION
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE TABLE_NAME = 'your_table' 
AND CONSTRAINT_NAME = 'PRIMARY';

-- Remove primary key if needed (be careful!)
ALTER TABLE your_table DROP PRIMARY KEY;

-- Add new primary key
ALTER TABLE your_table ADD PRIMARY KEY (new_column);

Advanced Primary Key Strategies

Snowflake IDs for Distributed Systems

For high-scale distributed applications, consider implementing Snowflake-style IDs:

-- Custom function to generate Snowflake IDs (MySQL example)
DELIMITER //
CREATE FUNCTION generate_snowflake_id(machine_id INT)
RETURNS BIGINT
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE result BIGINT;
    DECLARE timestamp_part BIGINT;
    DECLARE sequence_part INT DEFAULT 0;
    
    SET timestamp_part = (UNIX_TIMESTAMP(NOW()) - 1609459200) * 1000; -- Epoch: 2021-01-01
    SET result = (timestamp_part << 22) | (machine_id << 12) | sequence_part;
    
    RETURN result;
END //
DELIMITER ;

-- Use in table creation
CREATE TABLE distributed_events (
    id BIGINT PRIMARY KEY DEFAULT (generate_snowflake_id(1)),
    event_type VARCHAR(50),
    payload JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Handling Primary Key Conflicts in Replication

For master-master replication setups:

-- MySQL: Set different auto-increment offsets per server
-- Server 1:
SET GLOBAL auto_increment_offset = 1;
SET GLOBAL auto_increment_increment = 2;

-- Server 2:
SET GLOBAL auto_increment_offset = 2;
SET GLOBAL auto_increment_increment = 2;

-- This ensures Server 1 generates 1,3,5,7... and Server 2 generates 2,4,6,8...

Integration with Modern Development Practices

Primary keys play crucial roles in modern development workflows. When using ORMs like Django, Rails, or Entity Framework, understand how they handle primary keys:

/* Django models.py */
from django.db import models
import uuid

class User(models.Model):
    # Django creates 'id' auto-increment primary key by default
    username = models.CharField(max_length=50, unique=True)
    email = models.EmailField(unique=True)

class Session(models.Model):
    # Custom UUID primary key
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    user = models.ForeignKey(User, on_delete=models.CASCADE)
    created_at = models.DateTimeField(auto_now_add=True)

For API design, primary keys become resource identifiers:

// RESTful endpoints using primary keys
GET    /api/users/123           // Get user by primary key
PUT    /api/users/123           // Update user by primary key
DELETE /api/users/123           // Delete user by primary key
GET    /api/orders/456/items/789 // Composite key: order 456, item 789

Understanding primary keys deeply will make you a better database designer and help you avoid costly mistakes in production systems. Whether you're building a simple web application or a complex distributed system, proper primary key strategy is essential for maintainable, performant databases.

For more detailed information about primary key implementation in specific database systems, check out the official documentation for MySQL Primary Key Optimization, PostgreSQL Primary Key Constraints, and SQL Server Primary and Foreign Key Constraints.

This article incorporates information and material from various online sources. We acknowledge and appreciate the work of all original authors, publishers, and websites. While every effort has been made to appropriately credit the source material, any unintentional oversight or omission does not constitute a copyright infringement. All trademarks, logos, and images mentioned are the property of their respective owners. If you believe that any content used in this article infringes upon your copyright, please contact us immediately for review and prompt action.

This article is intended for informational and educational purposes only and does not infringe on the rights of the copyright owners. If any copyrighted material has been used without proper credit or in violation of copyright laws, it is unintentional and we will rectify it promptly upon notification. Please note that the republishing, redistribution, or reproduction of part or all of the contents in any form is prohibited without express written permission from the author and website owner. For permissions or further inquiries, please contact us.