VMware ESXi and vSphere Cluster Management

MySQL Primary Keys: Unique Row Identification and AUTO_INCREMENT

Learn how MySQL primary keys uniquely identify rows, enforce data integrity, use AUTO_INCREMENT, and support single-column and composite key designs.

What Is a Primary Key?

A primary key is a database constraint that identifies each row in a table unambiguously. It can be one column or a combination of several columns.

For example, a buyer may have a username, city, and country. These fields describe the buyer, but they are not necessarily reliable row identifiers. A username might change, two people might share a city, and a country is certainly not unique. An integer id generated specifically for each buyer is usually a better identifier.

Reliable row identity matters when you need to:

• Query one exact row.
• Update or delete the intended row without affecting another row.
• Connect rows in related tables.
• Prevent accidental duplicate records.
• Preserve data integrity as the application grows.

The SQL keyword used to define this constraint is PRIMARY KEY.

Primary Key Rules in MySQL

MySQL applies several important rules to a primary key:

NULL means a missing or unknown value. It is different from an empty string such as '' and different from zero. None of these distinctions change the primary-key rule: a primary key cannot contain NULL.

Single-Column Primary Keys

A common design uses an integer column named id as the table's primary key. The key can be declared inline in the column definition:

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  price DECIMAL(10, 2)
);

In this example, id is the primary key. MySQL prevents two rows from having the same id, and it prevents an insert that supplies NULL for id. A primary key also creates the table's main index for locating rows efficiently.

You can also declare the key separately, which is especially useful when the key contains multiple columns:

CREATE TABLE products (
  id INT NOT NULL,
  name VARCHAR(100),
  price DECIMAL(10, 2),
  PRIMARY KEY (id)
);

AUTO_INCREMENT Primary Keys

AUTO_INCREMENT is a MySQL column attribute that generates a new numeric value when an insert omits that column. It is commonly used with an integer primary key:

id INT AUTO_INCREMENT PRIMARY KEY

When a new row is inserted without an id, MySQL chooses the next available generated value according to the table's auto-increment behavior. A typical sequence might be 1, 2, 3, and so on.

The generated number is a surrogate key: an identifier created for the database that has no direct business meaning. It should identify a row, not represent a customer's rank, purchase count, or position in a report.

Generated values do not have to be perfectly consecutive. Deleted rows, failed inserts, transactions that roll back, and internal allocation behavior can leave gaps. For example, values might progress from 1 to 2 to 4. This is normal and is not evidence that the table is corrupt.

A natural key uses meaningful real-world data, such as a stable country code or an email address. A natural key can be appropriate when the value is genuinely stable and unique. However, descriptive data may change or may later prove not to be unique, so many designs use a surrogate id as the primary key and place a separate UNIQUE constraint on a business field when necessary.

Creating a Buyers Table

The following table uses an integer id as a generated primary key. The text columns have lengths suitable for ordinary sample attributes:

CREATE TABLE buyers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(40),
  city VARCHAR(255),
  country VARCHAR(255)
);

Here, username, city, and country are ordinary attributes. The id column is the stable row identifier. MySQL enforces both uniqueness and non-nullability because of PRIMARY KEY; AUTO_INCREMENT supplies values when they are omitted.

Inserting Rows with Generated Identifiers

When using AUTO_INCREMENT, leave id out of the column list:

INSERT INTO buyers (username, city, country)
VALUES
  ('john', 'London', 'UK'),
  ('mark', 'Berlin', 'Germany'),
  ('alejandra', 'Madrid', 'Spain');

MySQL supplies the identifiers automatically. You can inspect the result with:

SELECT * FROM buyers;

Example output might look like this:

The exact starting value depends on the table's current auto-increment state. The important result is that each row receives a distinct identifier.

Duplicate Primary-Key Values

You can explicitly provide an identifier, but it must not already exist:

INSERT INTO buyers (id, username, city, country)
VALUES (10, 'sara', 'Rome', 'Italy');

A second insert using id = 10 fails with a duplicate-entry error because two rows cannot share the same primary-key value:

INSERT INTO buyers (id, username, city, country)
VALUES (10, 'lee', 'Paris', 'France');

For ordinary inserts, omit the auto-increment column and let MySQL generate the identifier.

Testing NULL Rejection

For a non-auto-increment key, an explicit NULL value is rejected:

CREATE TABLE manual_items (
  item_id INT PRIMARY KEY,
  description VARCHAR(100)
);

INSERT INTO manual_items (item_id, description)
VALUES (NULL, 'Example');

The insert fails because a primary key cannot be NULL. With an auto-increment key, omitting the column is the normal way to request a generated value; omitting it is not the same as supplying an unknown key.

Adding a Primary Key to an Existing Table

A primary key can be added after a table has been created. Before doing so, inspect the existing data. Every proposed key value must be present, non-null, and unique.

If an existing column already contains suitable identifiers, add the constraint with ALTER TABLE:

ALTER TABLE buyers
ADD PRIMARY KEY (id);

If the existing table has no identifier column, add an integer auto-increment column and make it the primary key:

ALTER TABLE buyers
ADD COLUMN id INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

Adding a generated identifier to a populated table is a schema change. Test it on a copy or in a controlled migration, and verify the result:

SHOW CREATE TABLE buyers;

If an id column already exists but is not yet auto-incrementing, its definition may need to be changed separately, depending on its current type and constraints. Always inspect the current definition before altering it.

Composite Primary Keys

A composite primary key is one primary key made from two or more columns. Uniqueness applies to the combination, not necessarily to each column individually.

An enrollment table is a simple example. One student can enroll in many courses, and one course can contain many students. The pair (student_id, course_id) identifies one enrollment:

CREATE TABLE enrollments (
  student_id INT NOT NULL,
  course_id INT NOT NULL,
  enrolled_at DATETIME NOT NULL,
  PRIMARY KEY (student_id, course_id)
);

These rows are valid because each pair is different:

INSERT INTO enrollments (student_id, course_id, enrolled_at)
VALUES
  (7, 101, '2026-01-10 09:00:00'),
  (7, 102, '2026-01-11 09:00:00'),
  (8, 101, '2026-01-12 09:00:00');

Student 7 appears more than once, and course 101 appears more than once. That is allowed because the pairs differ. A second row with (7, 101) would be rejected because that exact enrollment already exists.

PRIMARY KEY, UNIQUE, and AUTO_INCREMENT

PRIMARY KEY and UNIQUE both prevent duplicate values where their constraints apply. The primary key is specifically the table's main row identifier and cannot be NULL. A table may have several unique constraints but only one primary key.

AUTO_INCREMENT is an attribute for generating numeric values. It does not replace the primary-key constraint. For example, an auto-increment column without a primary key or unique constraint is not, by that fact alone, guaranteed to identify rows uniquely.

A foreign key is a column or set of columns whose values reference a primary-key value in another table. For example, an orders table might contain buyer_id that references buyers.id. This connects related rows and helps enforce referential integrity.

Troubleshooting Primary-Key Problems

Duplicate entry error

Likely cause: The supplied key value already exists.

Resolution: Use a different value, or omit an AUTO_INCREMENT identifier so MySQL generates one.

Cannot add a primary key

Likely cause: Existing rows contain duplicate or NULL values in the proposed key column.

Resolution: Find and correct the data before running ALTER TABLE. For example, inspect possible duplicates with:

SELECT id, COUNT(*) AS occurrences
FROM buyers
GROUP BY id
HAVING COUNT(*) > 1;

Also check for missing values:

SELECT *
FROM buyers
WHERE id IS NULL;

AUTO_INCREMENT values have gaps

Likely cause: Rows were deleted, inserts failed, transactions rolled back, or values were allocated without becoming permanent rows.

Resolution: Treat the identifier as a stable row key, not as a count or a business sequence. Do not renumber rows merely to remove gaps.

Multiple primary keys are defined

Likely cause: A table already has a primary key, or multiple column definitions incorrectly declare separate primary keys.

Resolution: Keep one PRIMARY KEY constraint. If several columns jointly identify a row, use one composite declaration such as PRIMARY KEY (student_id, course_id). If another field needs independent uniqueness, use a UNIQUE constraint.

A changeable business field is used as the key

Likely cause: A natural key such as a username or email address may change or may not remain unique.

Resolution: Consider a stable surrogate id primary key. Add a separate UNIQUE constraint to the business field if duplicates must be prevented.

Exam-Relevant Summary

• A primary key identifies each table row uniquely.
• Primary-key values must be unique and cannot be NULL.
• A table has one primary-key constraint, which may contain one or multiple columns.
• AUTO_INCREMENT generates numeric values when an insert omits the identifier.
• Auto-generated identifiers are usually surrogate keys and do not need to be consecutive.
• A composite primary key enforces uniqueness on the combination of its columns.
• UNIQUE provides additional uniqueness but is not the table's main primary key.
• A foreign key references a key in another table to represent a relationship.
• Inspect existing data before adding a primary key with ALTER TABLE.

For related practice, review MySQL primary-key examples and apply the same principles to unique constraints, foreign keys, and junction tables.