Primary Key & AUTO_INCREMENT0%

Primary Key & AUTO_INCREMENT

Beginner12 min read•Updated: 2026-09-12

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
Uniqueness: No two rows can possess the same primary key value.
2
Non-Nullability: A primary key column can never contain a NULL value.

1-- Defining a Primary Key in column definition:
2CREATE TABLE departments (
3    department_id INT PRIMARY KEY,
4    department_name VARCHAR(50) NOT NULL
5);
6 
7-- Defining a Primary Key at the table level (Best Practice):
8CREATE TABLE departments (
9    department_id INT NOT NULL,
10    department_name VARCHAR(50) NOT NULL,
11    CONSTRAINT pk_departments PRIMARY KEY (department_id)
12);

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:

1CREATE TABLE employees (
2    employee_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
3    first_name VARCHAR(50) NOT NULL,
4    last_name VARCHAR(50) NOT NULL,
5    salary DECIMAL(10, 2) NOT NULL
6) 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:

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

4. Composite Primary Keys

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

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

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.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

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

Question 2

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

Question 3

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

Question 4

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

Question 5

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

Next Lesson

Foreign Key & Referential Integrity

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

Related Lessons

Previous Lesson	Next Lesson
MySQL Storage Engines: InnoDB vs MyISAM	Foreign Key & Referential Integrity

Practice Quiz

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

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Foreign Key & Referential Integrity

Primary Key & AUTO_INCREMENT

Beginner12 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Primary Key & AUTO_INCREMENT: Uniquely Identifying Records

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
Uniqueness: No two rows can possess the same primary key value.
2
Non-Nullability: A primary key column can never contain a NULL value.

1-- Defining a Primary Key in column definition:
2CREATE TABLE departments (
3    department_id INT PRIMARY KEY,
4    department_name VARCHAR(50) NOT NULL
5);
6 
7-- Defining a Primary Key at the table level (Best Practice):
8CREATE TABLE departments (
9    department_id INT NOT NULL,
10    department_name VARCHAR(50) NOT NULL,
11    CONSTRAINT pk_departments PRIMARY KEY (department_id)
12);

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:

1CREATE TABLE employees (
2    employee_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
3    first_name VARCHAR(50) NOT NULL,
4    last_name VARCHAR(50) NOT NULL,
5    salary DECIMAL(10, 2) NOT NULL
6) 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:

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

4. Composite Primary Keys

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

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

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.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

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

Question 2

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

Question 3

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

Question 4

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

Question 5

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

Next Lesson

Foreign Key & Referential Integrity

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

Related Lessons

Previous Lesson	Next Lesson
MySQL Storage Engines: InnoDB vs MyISAM	Foreign Key & Referential Integrity

Practice Quiz

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

Previous Next

Primary Key & AUTO_INCREMENT: Uniquely Identifying Records

1. What is a Primary Key?

2. Natural vs Surrogate Keys

3. The AUTO_INCREMENT Attribute

Inserting Data with AUTO_INCREMENT:

4. Composite Primary Keys

5. Best Practices & Common Pitfalls

Multiple Choice Questions

Foreign Key & Referential Integrity

Related Lessons

Practice Quiz

Loading Page Content...

Primary Key & AUTO_INCREMENT: Uniquely Identifying Records

1. What is a Primary Key?

2. Natural vs Surrogate Keys

3. The AUTO_INCREMENT Attribute

Inserting Data with AUTO_INCREMENT:

4. Composite Primary Keys

5. Best Practices & Common Pitfalls

Multiple Choice Questions

Foreign Key & Referential Integrity

Related Lessons

Practice Quiz

3. The `AUTO_INCREMENT` Attribute

3. The `AUTO_INCREMENT` Attribute