How to Add a Primary Key in MySQL

How do I add a primary key to an existing MySQL table?

ADD PRIMARY KEY creates or changes the table’s primary-key constraint, enforcing unique, non-NULL values and building a supporting index.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why is a primary key crucial in MySQL?

Primary keys guarantee row uniqueness, enable fast lookups through an automatically created index, and allow other tables to reference the row with foreign-key constraints.

How do I add a primary key to an existing table?

Run ALTER TABLE <table> ADD PRIMARY KEY (col1[, col2…]);.MySQL first checks that all listed columns are NOT NULL and unique, then builds the index.

ALTER TABLE Customers ADD PRIMARY KEY (id);

What if the column contains NULLs or duplicates?

The statement fails.Clean data with UPDATE or DELETE, or create a new surrogate key column and populate it before retrying.

How do I add a composite primary key?

List multiple columns in the same order you expect to query them.

ALTER TABLE OrderItems ADD PRIMARY KEY (order_id, product_id);

Can I set the primary key during table creation?

Yes—define it inside CREATE TABLE.

CREATE TABLE Products ( id INT AUTO_INCREMENT, name VARCHAR(100) NOT NULL, price DECIMAL(10,2) NOT NULL, stock INT DEFAULT 0, PRIMARY KEY (id) );

Best practices for adding primary keys

Use immutable, numeric columns where possible, avoid business-logic columns that might change, and keep the key short to improve index performance.

Why How to Add a Primary Key in MySQL is important

How to Add a Primary Key in MySQL Example Usage


-- Add a composite primary key to OrderItems so each product appears only once per order
ALTER TABLE OrderItems ADD PRIMARY KEY (order_id, product_id);

-- Verify
SHOW CREATE TABLE OrderItems;

How to Add a Primary Key in MySQL Syntax


-- Single-column primary key
ALTER TABLE Customers ADD PRIMARY KEY (id);

-- Composite primary key on bridge table
ALTER TABLE OrderItems ADD PRIMARY KEY (order_id, product_id);

-- Add surrogate key when existing columns contain duplicates
ALTER TABLE Orders 
  ADD COLUMN order_pk BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;

-- Inline definition during table creation
CREATE TABLE Orders (
  id            INT UNSIGNED NOT NULL,
  customer_id   INT UNSIGNED NOT NULL,
  order_date    DATE         NOT NULL,
  total_amount  DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (id)
);

Common Mistakes

Adding a primary key to a column that still allows NULLs. MySQL rejects the command. Fix by running <code>ALTER TABLE ... MODIFY col INT NOT NULL;</code> first.
Forgetting to deduplicate data before adding the key. Duplicate values cause a duplicate-entry error. Remove duplicates or create a new AUTO_INCREMENT column.