Introduction

Adding a primary key to an existing MySQL table is easy only when the data already satisfies the primary-key rules. The candidate column, or column combination, must be unique and non-null before ALTER TABLE ... ADD PRIMARY KEY can succeed.

Check the existing data first

A primary key is both a uniqueness constraint and the table's main row identifier. If the current data has duplicates or NULL values, MySQL will reject the schema change.

Suppose the table looks like this:

1CREATE TABLE orders (
2    order_id INT,
3    customer_email VARCHAR(255),
4    created_at DATETIME
5);

Before promoting order_id to the primary key, check for duplicates:

1SELECT order_id, COUNT(*) AS occurrences
2FROM orders
3GROUP BY order_id
4HAVING COUNT(*) > 1;

Then check for missing values:

SELECT *
FROM orders
WHERE order_id IS NULL;

If either query returns rows, fix the data first. The correct fix might be cleanup, deduplication, or adding a new surrogate key column if the existing business field is not stable enough to be the row identifier.

Add the primary key with ALTER TABLE

Once the candidate column is valid, the schema change itself is simple:

ALTER TABLE orders
ADD PRIMARY KEY (order_id);

MySQL automatically creates the supporting index. You can verify the result with:

SHOW CREATE TABLE orders;

If new rows should receive generated numeric identifiers, modify the column first:

1ALTER TABLE orders
2MODIFY order_id INT NOT NULL AUTO_INCREMENT;
3
4ALTER TABLE orders
5ADD PRIMARY KEY (order_id);

That is a common migration path when an older table was created without proper identity semantics.

Composite primary keys are valid too

A primary key does not have to be one column. Join tables often use a composite primary key because the row identity is the combination itself.

1CREATE TABLE enrollments (
2    student_id INT NOT NULL,
3    course_id INT NOT NULL,
4    enrolled_at DATETIME NOT NULL
5);
6
7ALTER TABLE enrollments
8ADD PRIMARY KEY (student_id, course_id);

This works well when the paired values are short, stable, and already define uniqueness naturally.

If downstream tables need to reference this table often, or if one part of the composite key may change, a separate surrogate key can still be more convenient.

Sometimes a new key column is the better solution

If the existing data column contains duplicates, can change over time, or has business meaning that should remain editable, do not force it into the role of primary key. Add a new identity column instead.

ALTER TABLE orders
ADD COLUMN id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

Then keep the business column separately constrained with UNIQUE if needed. This usually produces a cleaner schema for foreign keys and long-term maintenance.

Common Pitfalls

• Trying to add a primary key before checking for duplicates or NULL values.
• Forgetting that a table can have only one primary key at a time.
• Confusing UNIQUE with PRIMARY KEY and treating them as interchangeable.
• Adding a natural key that may change later and then regretting it when foreign keys depend on it.
• Running a large ALTER TABLE in production without considering lock time or migration impact.

Summary

• A MySQL primary key candidate must already be unique and non-null.
• Check the data first, then use ALTER TABLE ... ADD PRIMARY KEY (...).
• Add AUTO_INCREMENT when the key should generate new numeric row identifiers.
• Composite primary keys are valid when row identity naturally depends on multiple columns.
• If the existing column is unstable or duplicated, add a new surrogate key instead of forcing a bad primary key.