MySQL Primary Keys

Learn how MySQL primary keys uniquely identify rows, use AUTO_INCREMENT IDs, add keys to existing tables, and create composite primary keys.

A primary key is a table constraint that uniquely identifies every row in a table. It can be one column, such as id, or a combination of multiple columns.

Stable row identification matters when you store, find, update, delete, and relate records. For example, a username or city may change or may not be unique. A primary key gives each row a reliable identity that other tables can reference.

A primary key is more than a naming convention. The PRIMARY KEY clause tells MySQL to enforce rules that protect the table's data integrity.

Primary key rules

Primary-key values must be unique. No two rows can have the same primary-key value.
Primary-key columns cannot contain NULL. NULL means that a value is absent.
A table can define only one primary key.
The one primary key can contain one column or several columns. A key made from several columns is called a composite primary key.

These rules apply whether the key is declared while creating a table or added later.

Creating a table with a primary key

Use CREATE TABLE to define a new table, its columns, and its constraints. In this example, id is an integer primary key, while the other columns describe each buyer.

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

The inline declaration id INT PRIMARY KEY defines the column and declares it as the table's primary key in the same line.

A primary key can also be declared separately at the end of the table definition. This form is especially useful for composite keys:

CREATE TABLE example_table (
    first_column INT NOT NULL,
    second_column INT NOT NULL,
    PRIMARY KEY (first_column, second_column)
);

Using AUTO_INCREMENT identifiers

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.

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

Under normal settings, generated IDs begin at 1 and advance for successive inserted rows. The exact next value depends on the table's existing data and configuration.

Important: AUTO_INCREMENT generates identifier values, but it does not replace the PRIMARY KEY constraint. The primary key enforces uniqueness and disallows NULL; AUTO_INCREMENT supplies numeric values when they are omitted.

Application code can therefore leave out the id column when inserting a buyer.

Inserting rows without the ID column

Specify the descriptive columns, but omit id:

INSERT INTO buyers (username, city, country)
VALUES ('john', 'London', 'UK');

INSERT INTO buyers (username, city, country)
VALUES ('sara', 'Paris', 'France');

INSERT INTO buyers (username, city, country)
VALUES ('mike', 'Toronto', 'Canada');

MySQL supplies the primary-key values automatically. Query the table to verify the results:

SELECT * FROM buyers;

id | username | city | country

1 | john | London | UK

2 | sara | Paris | France

3 | mike | Toronto | Canada

The displayed IDs are distinct, so each row has its own identifier. Do not treat these values as a guaranteed gap-free count. Deleted rows or failed inserts can leave gaps, and that is normal for an identifier column.

Adding a primary key to an existing table

If a table was created without a primary key, you can add one with ALTER TABLE:

ALTER TABLE buyers ADD PRIMARY KEY (id);

Before running this statement, existing values in id must all be unique and non-NULL. The table must also not already have a primary key.

For a table with existing data, inspect and repair duplicate or missing values before adding the constraint. If no single column is suitable, a combination of columns may form an appropriate composite primary key.

Composite primary keys

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

For example, an enrollment table can record which students take which courses. A student may enroll in many courses, and a course may have many students. The pair of IDs identifies one enrollment:

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

In this design, student_id can repeat and course_id can repeat. However, the same combination of student_id and course_id cannot appear twice.

key type | key columns | what must be unique | example use

Single-column | id | Each id value | Identifying one buyer

Composite | student_id, course_id | Each pair of values | Identifying one student-course relationship

Primary keys compared with related concepts

Constraint: a database rule that MySQL enforces to preserve data integrity. A primary key is one type of constraint.
Unique: a property requiring that no two rows share the same key value or key combination.
NULL: the absence of a value. Primary-key columns cannot contain it.
AUTO_INCREMENT: a MySQL feature for generating successive numeric values. It does not itself define a primary key.

Troubleshooting primary-key errors

Duplicate primary-key value

An insert or key definition fails when two rows would have the same primary-key value. Choose a distinct value, use AUTO_INCREMENT for generated numeric IDs, or correct duplicate existing data before adding the key.

NULL primary-key value

An insert fails when a primary-key column receives NULL. Supply a valid non-NULL value, or define a suitable auto-incrementing numeric key and omit that column from the insert.

ALTER TABLE ... ADD PRIMARY KEY fails

The candidate column may contain duplicates or NULL values, or the table may already have a primary key. Inspect and repair the data, choose a suitable unique non-NULL column or column combination, and ensure that only one primary key is defined.

Generated IDs contain gaps

AUTO_INCREMENT is intended to generate unique identifiers, not gap-free numbering. Deletions and failed inserts can cause gaps. Treat the value as an identifier rather than as a row count or business sequence.

Exam-relevant summary

PRIMARY KEY uniquely identifies every row.
A primary key cannot contain duplicate values or NULL.
Each table has only one primary key, but that key may contain multiple columns.
AUTO_INCREMENT generates numeric IDs when the ID column is omitted from an insert.
Use ALTER TABLE ... ADD PRIMARY KEY to add a key later, after checking existing data.
In a composite key, uniqueness applies to the complete combination of column values.

For related practice, review Create A Table, Insert New Records, Modify A Table, and Data Types.