Primary Keys: The Foundation of Data Integrity

Every well-designed table needs a primary key—a column (or set of columns) that uniquely identifies each row. Primary keys are the cornerstone of database integrity, enabling relationships between tables and ensuring that no two rows can be confused with each other. In this lesson, you'll learn what makes a good primary key, how to implement them, and why they're absolutely essential for professional database design.

📚 What You'll Learn

• What a primary key is and why every table needs one
• The four essential rules for primary keys
• How primary keys enable table relationships
• How primary keys support efficient queries and joins
• Natural keys vs. surrogate keys (and when to use each)
• Single-column vs. composite primary keys
• How to create primary keys in SQL
• Common primary key mistakes to avoid
• Best practices for choosing primary keys

1. What is a Primary Key?

Example: Students Table

In this table, student_id is the primary key. Each student has a unique ID that will never change and will never be shared with another student.

2. The Four Golden Rules of Primary Keys

For a column to serve as a primary key, it must follow these four essential rules:

❌ What Makes a BAD Primary Key?

3. Why Every Table MUST Have a Primary Key

Reason 1: Uniquely Identify Rows

Without a primary key, you cannot reliably reference a specific row. Imagine trying to update "the student named John Smith" when you have three students with that name!

UPDATE students 
SET major = 'Data Science' 
WHERE name = 'John Smith';

-- Which John Smith? All three get updated! 😱

UPDATE students 
SET major = 'Data Science' 
WHERE student_id = 100234;

-- Exactly one row updated! ✓

Reason 2: Enable Relationships (Foreign Keys)

Foreign keys reference primary keys. Without primary keys, you cannot create relationships between tables!

Reason 3: Fast Lookups and Joins

Databases automatically create an index on the primary key, making queries extremely fast. Looking up a row by primary key is nearly instantaneous, even in tables with millions of rows.

Reason 4: Prevent Duplicate Data

The database enforces uniqueness automatically. You cannot accidentally insert two students with the same student_id—the database will reject it.

4. Natural Keys vs. Surrogate Keys

Natural Keys: Using Real-World Identifiers

✅ Advantages of Natural Keys:

• Meaningful to users (ISBN tells you it's a book identifier)
• No need to join to another table to see the identifier
• One less column in the database

❌ Disadvantages of Natural Keys:

• Can change: SSNs can be reissued, license plates change
• Can be NULL: Not everyone has an SSN when first entering the system
• Privacy concerns: SSNs shouldn't be widely shared
• Composite keys: Natural keys are often multiple columns

Surrogate Keys: Database-Generated IDs

✅ Advantages of Surrogate Keys:

• Never changes: Once assigned, stays forever
• Always unique: Database guarantees it
• Never NULL: Auto-generated on insert
• Simple: Single integer column
• Fast: Integers are compact and join quickly
• No privacy issues: No business meaning to protect

❌ Disadvantages of Surrogate Keys:

• No business meaning (student_id 100234 doesn't tell you who they are)
• Extra column in the database

5. Creating Primary Keys in SQL

Method 1: Define at Column Level

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    major VARCHAR(100)
);

Method 2: Define at Table Level (More Flexible)

CREATE TABLE students (
    student_id INT AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    major VARCHAR(100),
    PRIMARY KEY (student_id)
);

Composite Primary Key (Multiple Columns)

Sometimes you need multiple columns to uniquely identify a row:

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    enrollment_date DATE NOT NULL,
    grade VARCHAR(2),
    PRIMARY KEY (student_id, course_id)
);

This composite key ensures that each student can enroll in each course only once. The combination of (student_id, course_id) must be unique.

Adding Primary Key to Existing Table

-- Add primary key after table creation
ALTER TABLE students
ADD PRIMARY KEY (student_id);

-- Add auto-increment to existing column
ALTER TABLE students
MODIFY student_id INT AUTO_INCREMENT;

6. Common Primary Key Mistakes

7. 🎯 Practice Exercises

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    stock_quantity INT NOT NULL DEFAULT 0
);

-- Verify the structure
DESCRIBE products;

-- ❌ Broken table
CREATE TABLE employees (
    email VARCHAR(255),
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    hire_date DATE
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) UNIQUE NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    hire_date DATE NOT NULL
);

-- Now you can reference employees:
-- SELECT * FROM employees WHERE employee_id = 42;

CREATE TABLE course_prerequisites (
    course_id INT NOT NULL,
    prerequisite_course_id INT NOT NULL,
    PRIMARY KEY (course_id, prerequisite_course_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id),
    FOREIGN KEY (prerequisite_course_id) REFERENCES courses(course_id),
    CHECK (course_id != prerequisite_course_id)
);

-- Example data:
-- CS201 requires CS101
-- INSERT INTO course_prerequisites VALUES (201, 101);
-- CS301 requires both CS201 and MATH150
-- INSERT INTO course_prerequisites VALUES (301, 201);
-- INSERT INTO course_prerequisites VALUES (301, 150);

CREATE TABLE books (
    isbn VARCHAR(20) PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author VARCHAR(255) NOT NULL,
    publication_year INT
);

CREATE TABLE books (
    book_id INT PRIMARY KEY AUTO_INCREMENT,
    isbn VARCHAR(20) UNIQUE,
    title VARCHAR(255) NOT NULL,
    author VARCHAR(255) NOT NULL,
    publication_year INT
);

📝 Key Takeaways

• Every table needs a primary key to uniquely identify rows
• Primary keys must be unique, not NULL, unchanging, and simple
• Primary keys enable relationships (foreign keys) between tables
• Primary keys are automatically indexed for fast lookups
• Surrogate keys (AUTO_INCREMENT IDs) are preferred over natural keys
• Use INT AUTO_INCREMENT for most primary keys
• Composite keys use multiple columns (rare, but sometimes necessary)
• Never use columns that can change or be NULL as primary keys
• Primary keys prevent duplicate data and enable efficient queries
• Without primary keys, you cannot reliably update, delete, or reference specific rows