Skip to main content
MySQL Basics
CHAPTER 12 Beginner

Primary Keys and Auto Increment

Updated: May 16, 2026
6 min read

# CHAPTER 12

Primary Keys and Auto Increment

1. Introduction

Imagine a company with two employees named "John Smith". One is the CEO, the other is an intern. If you write UPDATE employees SET salary = 1000000 WHERE firstname = 'John' AND lastname = 'Smith', you just gave the intern a million-dollar raise. Names are not unique. Emails can change. How do databases guarantee that an UPDATE or DELETE targets exactly one, highly specific row without fail? They use Primary Keys. In this chapter, we will master the absolute foundation of relational database architecture.

2. Learning Objectives

By the end of this chapter, you will be able to:
  • Define the rules of a Primary Key constraint.
  • Implement the AUTOINCREMENT feature.
  • Understand the UNIQUE constraint.
  • Update table schemas to include Primary Keys.
  • Prevent duplicate data insertions.

3. What is a Primary Key?

A Primary Key (PK) is a specific column in a table designated to uniquely identify every single row. It operates under two unbreakable mathematical rules:
  1. 1. It must be UNIQUE: No two rows can ever have the same Primary Key.
  1. 2. It cannot be NULL: A Primary Key can never be empty or blank.

*Example:* Your Social Security Number or your Student ID is a real-world Primary Key. It identifies *you* and only you.

4. The AUTOINCREMENT Feature

If we require a unique ID for every user, how do we generate it? Do we ask the web application to check what the last ID was, add 1 to it, and send it back? No, that causes race conditions when 1,000 users register at the same millisecond. MySQL handles this natively using AUTOINCREMENT.

When a column is set to AUTOINCREMENT, MySQL takes complete control of that column. Whenever you INSERT a new row, MySQL automatically looks at the highest ID, adds 1, and assigns it safely.

sql
123456 
-- Creating a perfect, production-ready table
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,  -- The Magic Line!
    first_name VARCHAR(50),
    email VARCHAR(100)
);

5. Inserting Data with Auto Increment

Because MySQL controls the id column, you completely ignore it when writing INSERT statements!
sql
123456789101112 
-- Notice we DO NOT mention the 'id' column here.
INSERT INTO users (first_name, email) 
VALUES ('John', 'john@email.com');

INSERT INTO users (first_name, email) 
VALUES ('Sarah', 'sarah@email.com');

SELECT * FROM users;
-- Output:
-- id | first_name | email
-- 1  | John       | john...
-- 2  | Sarah      | sarah...

6. The UNIQUE Constraint

A Primary Key guarantees the id is unique. But what if we also want to guarantee that no two users can register with the exact same email address? We cannot make email the Primary Key (because tables can only have ONE Primary Key). Instead, we apply a UNIQUE constraint.
sql
12345 
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    email VARCHAR(100) UNIQUE   -- MySQL will reject any duplicate emails!
);

*If a user tries to sign up with an email that already exists, MySQL will instantly throw an error, preventing duplicate accounts at the database level!*

7. Adding a Primary Key to an Existing Table

If you created a table in the past and forgot the Primary Key, you can alter the table structure.
sql
1234567 
-- Add a primary key to an existing column
ALTER TABLE products 
ADD PRIMARY KEY (product_id);

-- Make an existing integer column Auto Increment
ALTER TABLE products 
MODIFY product_id INT AUTO_INCREMENT;

8. Common Mistakes

  • Using Emails or Usernames as Primary Keys: Beginners often think, "Emails are unique, I'll just use the email as the Primary Key!" This is a terrible design. If the user changes their email address later, the Primary Key changes, breaking all relationships to other tables (like Orders). Always use a meaningless, auto-incrementing INT (often just named id) as your Primary Key.
  • Assuming AutoIncrement fills gaps: If you insert ID 1, 2, and 3, and then delete ID 2, the next insert will be ID 4. MySQL will NOT go back and fill the empty ID 2 slot. IDs are strictly sequential and gaps are normal.

9. Best Practices

  • Every Table Must Have One: There is virtually no scenario in modern web development where a table should exist without a Primary Key column. It is a mandatory requirement for data integrity and high-speed searching.

10. Exercises

  1. 1. Write the SQL syntax to create a categories table with two columns: a self-managing categoryid that acts as the primary key, and a categoryname that cannot be duplicated.
  1. 2. Why should you avoid explicitly typing an ID number in an INSERT INTO statement if the column is set to AUTOINCREMENT?

11. MCQ Quiz with Answers

Question 1

What are the two strict rules that a column must follow if it is designated as a PRIMARY KEY?

Question 2

If you want to ensure that a column like phonenumber never contains duplicate values, but the table already has an id column serving as the Primary Key, what should you use?

12. Interview Questions

  • Q: Explain why using a Natural Key (like a User's Social Security Number or Email Address) as a Primary Key is generally considered a bad architectural practice compared to using a Surrogate Key (like an Auto-Incrementing Integer).
  • Q: Describe how MySQL's AUTOINCREMENT feature handles concurrency (e.g., when 500 users attempt to create an account at the exact same millisecond).

13. FAQs

Q: Can a table have multiple Primary Keys? A: A table can only have ONE Primary Key constraint. However, that single constraint can be composed of multiple columns combined together (called a Composite Primary Key). But for 99% of web development, a single id column is standard.

14. Summary

Primary Keys are the absolute anchor of relational database design. By utilizing AUTO_INCREMENT integers, you provide an immutable, mathematically perfect identity to every single piece of data in your application, ensuring that updates, deletions, and future relationships act with flawless precision.

15. Next Chapter Recommendation

Now that every row in our database has a permanent, unique ID tag, we can finally fulfill the promise of a "Relational" database. How do we link an Order to a specific User ID? In Chapter 13: Foreign Keys and Relationships, we will architect interconnected tables.

Finish this Chapter

Save your progress on your learning path and prepare for coding interview challenges.

Previous Chapter GROUP BY and HAVING Clauses
Next Chapter Foreign Keys and Relationships

Discussion

Join the discussion

Log in or create a free account to participate.

Sort: ·

Explore More

📖 Related Tutorials 6

❓ Related Quizzes 6