MySQL Primary Key

Last updated: October 19, 2026Oct 19, 2026

A primary key is the column (or set of columns) that uniquely identifies each row in a table, like a roll number in a class or an account number in a bank. Every table should have one. In MySQL's InnoDB engine the primary key also decides how rows are physically stored, so choosing it well affects performance too.

1. Primary Key Rules

Values must be unique: no two rows can share a key.
Values can't be NULL; MySQL makes key columns NOT NULL automatically.
A table has at most one primary key (which may span several columns).
The key should be stable: it shouldn't change once assigned.
MySQL automatically creates a unique index named PRIMARY for it.

2. Defining a Primary Key

Column-level, for a single column:

column-level.sql

CREATE TABLE students (
  roll_no INT PRIMARY KEY,
  name    VARCHAR(40) NOT NULL,
  class   VARCHAR(5)
);
INSERT INTO students VALUES (101, 'Aarohi', '9A'), (102, 'Bharat', '9B');

query.sql

INSERT INTO students VALUES (101, 'Chitra', '9A');
INSERT INTO students VALUES (NULL, 'Dhruv', '9B');

Output

ERROR 1062 (23000): Duplicate entry '101' for key 'students.PRIMARY'
ERROR 1048 (23000): Column 'roll_no' cannot be null

Table-level, which also lets you name it (MySQL always calls the primary key PRIMARY anyway):

table-level.sql

CREATE TABLE teachers (
  teacher_id INT,
  name       VARCHAR(40),
  PRIMARY KEY (teacher_id)
);

3. AUTO_INCREMENT Primary Keys

Most tables let MySQL generate the key. AUTO_INCREMENT gives each new row the next number:

auto-increment.sql

CREATE TABLE fee_payments (
  payment_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  roll_no    INT NOT NULL,
  amount     DECIMAL(8,2) NOT NULL
);
INSERT INTO fee_payments (roll_no, amount) VALUES (101, 15000), (102, 15000), (101, 2500);
SELECT * FROM fee_payments;
SELECT LAST_INSERT_ID() AS first_id_of_last_insert;

Result: 3 rows

payment_id	roll_no	amount
1	101	15000.00
2	102	15000.00
3	101	2500.00

Result: 1 row

first_id_of_last_insert
1

See MySQL Auto Increment for details.

4. Composite Primary Keys

When no single column is unique, combine columns. An attendance table has one row per student per day:

composite.sql

CREATE TABLE attendance (
  roll_no  INT,
  att_date DATE,
  present  BOOLEAN NOT NULL,
  PRIMARY KEY (roll_no, att_date)
);
INSERT INTO attendance VALUES (101,'2026-03-02',TRUE),(102,'2026-03-02',FALSE),(101,'2026-03-03',TRUE);
INSERT INTO attendance VALUES (101,'2026-03-02',FALSE);

Output

Query OK, 0 rows affected
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0
ERROR 1062 (23000): Duplicate entry '101-2026-03-02' for key 'attendance.PRIMARY'

Column order matters: the index on (roll_no, att_date) quickly finds one student's records, but not all students on a date. Put the column you search by most often first, or add a second index.

5. Natural vs Surrogate Keys

	Natural key	Surrogate key
What	A real-world value: Aadhaar, ISBN, email	A meaningless number or UUID generated by the system
Pros	No extra column; meaningful	Never changes; small and fast; no privacy issues
Cons	Can change (email), may be long, may be private	Extra column; meaning lives elsewhere
Advice	Store as a UNIQUE column	Use as the PRIMARY KEY in most tables

surrogate.sql

CREATE TABLE books (
  book_id INT AUTO_INCREMENT PRIMARY KEY,    -- surrogate key
  isbn    CHAR(13) NOT NULL UNIQUE,          -- natural key kept unique
  title   VARCHAR(80) NOT NULL
);
DESCRIBE books;

Result: 3 rows

Field	Type	Null	Key	Extra
book_id	int	NO	PRI	auto_increment
isbn	char(13)	NO	UNI
title	varchar(80)	NO

6. How InnoDB Uses the Primary Key

Figure 1: in InnoDB the primary key is the table's storage order (a clustered index).

Rows are stored in primary-key order, so range scans on the key are very fast.
Every secondary index stores the primary key value, so a long key makes all indexes bigger.
Inserting keys in increasing order (AUTO_INCREMENT) appends to the end; random keys (random UUIDs) split pages and fragment the table.
If you don't define a primary key, InnoDB uses the first UNIQUE NOT NULL index, or else creates a hidden 6-byte row ID you can't use in queries.

7. UUIDs as Primary Keys

UUIDs are handy for IDs generated outside the database, but 36-character strings are large. Store them in BINARY(16), with the time parts swapped to the front so new values arrive in order:

uuid.sql

CREATE TABLE devices (
  device_id BINARY(16) PRIMARY KEY,
  label     VARCHAR(30)
);
INSERT INTO devices VALUES (UUID_TO_BIN('3f1c9a3e-1f2b-11f0-9e4a-0242ac120002', 1), 'Lab tablet');
SELECT BIN_TO_UUID(device_id, 1) AS device_uuid, label FROM devices;

Result: 1 row

device_uuid	label
3f1c9a3e-1f2b-11f0-9e4a-0242ac120002	Lab tablet

8. Adding and Dropping a Primary Key

Add one to an existing table; the column must contain unique, non-NULL values:

add-pk.sql

CREATE TABLE clubs (club_code CHAR(4), name VARCHAR(30));
INSERT INTO clubs VALUES ('CHES','Chess'),('ROBO','Robotics');
ALTER TABLE clubs ADD PRIMARY KEY (club_code);
SHOW INDEX FROM clubs;

Result: 1 row

Key_name	Column_name	Non_unique
PRIMARY	club_code	0

Drop it with DROP PRIMARY KEY. If the key column is AUTO_INCREMENT, MySQL refuses, because an AUTO_INCREMENT column must be indexed:

query.sql

ALTER TABLE fee_payments DROP PRIMARY KEY;

Output

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

drop-pk.sql

ALTER TABLE clubs DROP PRIMARY KEY;
SHOW INDEX FROM clubs;   -- no indexes left

Output

Query OK, 2 rows affected
Records: 2  Duplicates: 0  Warnings: 0

Result: Empty set (0 rows)

Key_name	Column_name
No rows

Changing a primary key rebuilds the table and all its secondary indexes, so on large tables it takes time. Foreign keys pointing at the key must be dropped first.

9. Finding a Table's Primary Key

query.sql

SELECT table_name, column_name, ordinal_position
FROM information_schema.key_column_usage
WHERE table_schema = 'school' AND constraint_name = 'PRIMARY'
ORDER BY table_name, ordinal_position;

Result: 6 rows

TABLE_NAME	COLUMN_NAME	ORDINAL_POSITION
attendance	roll_no	1
attendance	att_date	2
books	book_id	1
devices	device_id	1
fee_payments	payment_id	1
students	roll_no	1

Tables without a primary key show up with this check:

query.sql

SELECT t.table_name
FROM information_schema.tables t
LEFT JOIN information_schema.table_constraints c
  ON c.table_schema = t.table_schema AND c.table_name = t.table_name AND c.constraint_type = 'PRIMARY KEY'
WHERE t.table_schema = 'school' AND t.table_type = 'BASE TABLE' AND c.constraint_name IS NULL;

Result: 1 row

TABLE_NAME
clubs

10. Summary

A primary key uniquely identifies each row; it is unique, NOT NULL and one per table.
Use INT/BIGINT AUTO_INCREMENT surrogate keys for most tables; keep natural keys as UNIQUE columns.
Composite keys combine columns, for example (student, date).
InnoDB stores rows in primary-key order, so keep keys short and increasing.
Add with ADD PRIMARY KEY (col); drop with DROP PRIMARY KEY.

Was this tutorial helpful?

Help Us Improve

MySQL Primary Key

1. Primary Key Rules

2. Defining a Primary Key

3. AUTO_INCREMENT Primary Keys

4. Composite Primary Keys

5. Natural vs Surrogate Keys

6. How InnoDB Uses the Primary Key

7. UUIDs as Primary Keys

8. Adding and Dropping a Primary Key

9. Finding a Table's Primary Key

10. Summary