Primary Key & AUTO_INCREMENT0%
Foreign Key & Referential Integrity

Primary Key & AUTO_INCREMENT

Beginner12 min read•Updated: 2026-09-12
Study Materials

Primary Key & AUTO_INCREMENT: Uniquely Identifying Records

In a relational database, no two rows should be identical duplicates. You must have a foolproof mechanism to distinguish one customer from another, even if two customers share the exact same name, birthdate, and city. This is the fundamental purpose of the Primary Key.

1. What is a Primary Key?

A Primary Key is a column (or combination of columns) that uniquely identifies each row in a table. It enforces two strict rules:

  1. 1
    Uniqueness: No two rows can possess the same primary key value.
  2. 2
    Non-Nullability: A primary key column can never contain a NULL value.
SQL
-- Defining a Primary Key in column definition:
CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(50) NOT NULL
);
 
-- Defining a Primary Key at the table level (Best Practice):
CREATE TABLE departments (
department_id INT NOT NULL,
department_name VARCHAR(50) NOT NULL,
CONSTRAINT pk_departments PRIMARY KEY (department_id)
);

2. Natural vs Surrogate Keys

  • Natural Key: A real-world attribute that is inherently unique (e.g., Passport Number, Vehicle VIN, Aadhaar Number).
  • Risk: Real-world attributes can change (a passport is reissued, a tax number changes formats), which breaks foreign key links across hundreds of tables.
  • Surrogate Key: An artificial, system-generated integer or UUID created purely for database identification (e.g., customer_id INT AUTO_INCREMENT).
  • Advantage: Highly performant, compact (4 or 8 bytes), never changes, and has zero business logic dependencies.

3. The AUTO_INCREMENT Attribute

In MySQL, the AUTO_INCREMENT keyword automatically generates a monotonically increasing sequential integer for each newly inserted row:

SQL
CREATE TABLE employees (
employee_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
salary DECIMAL(10, 2) NOT NULL
) AUTO_INCREMENT = 1001; -- Optional: Start sequence at 1001

Inserting Data with AUTO_INCREMENT:

When inserting, you omit the primary key column or pass NULL, and MySQL calculates the next value:

SQL
INSERT INTO employees (first_name, last_name, salary)
VALUES ('Kavita', 'Rao', 75000.00);
 
-- Query the exact ID generated by the most recent INSERT:
SELECT LAST_INSERT_ID();

4. Composite Primary Keys

A Composite Primary Key consists of two or more columns that together guarantee uniqueness:

SQL
-- Order Items: An order can have multiple products,
-- but the exact same product cannot appear twice in the same order!
CREATE TABLE order_items (
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL DEFAULT 1,
unit_price DECIMAL(10, 2) NOT NULL,
PRIMARY KEY (order_id, product_id) -- Composite Key!
);

5. Best Practices & Common Pitfalls

  • Avoid Using Strings/UUIDs as Primary Keys in High-Volume Tables: MySQL InnoDB stores table rows physically inside the B-Tree of the Primary Key (Clustered Index). Random string UUIDs (like v4 UUID) cause massive B-Tree page splits and fragmentation. Prefer sequential integers (BIGINT UNSIGNED AUTO_INCREMENT) or time-ordered UUIDs (UUID v7).
  • Be Mindful of Integer Exhaustion: If your application inserts millions of events daily, a standard signed INT caps out at 2.14 billion rows! Always use BIGINT UNSIGNED for high-volume event, log, or transaction tables.

Multiple Choice Questions

1. What two properties are strictly required of any column designated as a Primary Key?

A. It must be encrypted and accept NULL B. It must contain unique values for every row and cannot contain NULL C. It must be an alphabetical string D. It must be updated every 24 hours Answer: B Explanation: A primary key enforces uniqueness across all rows and strictly prohibits NULL values.

2. Which MySQL function returns the auto-increment integer generated by the most recent INSERT statement in the current session?

A. GET_CURRENT_ID() B. LAST_INSERT_ID() C. AUTO_INCREMENT_VAL() D. MAX(id) Answer: B Explanation: LAST_INSERT_ID() returns the first automatically generated value successfully inserted by an AUTO_INCREMENT column in the active connection.

3. What is an artificial primary key created purely for identification (with no business meaning) called?

A. Natural Key B. Surrogate Key C. Foreign Key D. Alternate Key Answer: B Explanation: A surrogate key is an artificially generated identifier (such as an auto-incrementing integer) devoid of business meaning.

4. What is a primary key that consists of two or more columns called?

A. Multi-Key B. Composite Primary Key C. Secondary Key D. Supercluster Key Answer: B Explanation: A composite primary key is a key composed of multiple columns that together guarantee unique identification for each row.

5. Why can using random UUID v4 strings as primary keys degrade write performance in large InnoDB tables?

A. UUIDs cannot be stored in MySQL B. Random UUID values cause severe B-Tree page splits and fragmentation in InnoDB clustered indexes C. UUIDs cannot be queried with SELECT D. UUIDs only allow 100 rows per table Answer: B Explanation: Because InnoDB physically orders rows on disk by the primary key, inserting non-sequential random UUIDs causes expensive page splits, disk fragmentation, and high I/O.

Next Lesson

Foreign Key & Referential Integrity

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous LessonNext Lesson
MySQL Storage Engines: InnoDB vs MyISAMForeign Key & Referential Integrity

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

PreviousNext
PrevNext