PRIMARY KEY

A primary key is the column (or columns) that uniquely identifies each row. In MySQL it is unique, can never be NULL, and there is at most one per table.

1
2
3
4
5
6
7

CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(100) NOT NULL UNIQUE,
    name VARCHAR(50) NOT NULL
);
INSERT INTO users (email, name) VALUES ('asha@example.com', 'Asha'), ('ben@example.com', 'Ben');
SELECT * FROM users;

1
2
3
4
5
6
7

+----+------------------+------+
| id | email            | name |
+----+------------------+------+
|  1 | asha@example.com | Asha |
|  2 | ben@example.com  | Ben  |
+----+------------------+------+
2 rows in set

Two ways to write it

1
2
3

CREATE TABLE a (id INT PRIMARY KEY, label VARCHAR(10));
CREATE TABLE b (id INT, label VARCHAR(10), PRIMARY KEY (id));
SHOW CREATE TABLE b\G

1
2
3
4
5
6
7
8

*************************** 1. row ***************************
       Table: b
Create Table: CREATE TABLE `b` (
  `id` int NOT NULL,
  `label` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set

The column form is fine for one column. A composite key needs the table-level form.

Composite primary key

A key made of several columns. The combination is unique, and each column alone may repeat:

1
2
3
4
5
6
7
8

CREATE TABLE seat_bookings (
    show_id INT NOT NULL,
    seat_number VARCHAR(4) NOT NULL,
    customer VARCHAR(20) NOT NULL,
    PRIMARY KEY (show_id, seat_number)
);
INSERT INTO seat_bookings VALUES (1, 'A1', 'Asha'), (1, 'A2', 'Ben'), (2, 'A1', 'Chitra');
INSERT INTO seat_bookings VALUES (1, 'A1', 'Dev');

1

ERROR 1062 (23000): Duplicate entry '1-A1' for key 'seat_bookings.PRIMARY'

Seat A1 can be booked once per show. The same seat for the same show twice is refused. Composite keys are natural for junction tables (Many-to-Many).

Surrogate vs natural keys

Recommendation: use a surrogate key (INT UNSIGNED AUTO_INCREMENT or BIGINT UNSIGNED) as the primary key, and put a UNIQUE constraint on the natural identifier (email, ISBN). Real-world “unique” values change or turn out not to be unique. A key that other tables reference should never change.

A natural key is a good choice for stable codes in lookup tables (country_code CHAR(2)).

InnoDB: the primary key is the table

In InnoDB the table is a B-tree sorted by primary key (the clustered index). The consequences:

1. Every secondary index contains the primary key values. A big primary key (a long VARCHAR, a CHAR(36) UUID) makes every index bigger.
2. Sequential keys insert fast at the end of the tree. Random keys (UUID v4) scatter inserts and cause page splits.
3. Lookups by primary key are the fastest lookups there are.
4. If you declare no primary key, InnoDB uses the first UNIQUE NOT NULL index, or creates a hidden 6-byte row id. Always declare a primary key yourself (Choosing the Right Data Type).

1
2
3
4

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

1
2
3
4
5
6
7

+---------------+-------------+------------------+
| TABLE_NAME    | COLUMN_NAME | ORDINAL_POSITION |
+---------------+-------------+------------------+
| seat_bookings | show_id     |                1 |
| seat_bookings | seat_number |                2 |
+---------------+-------------+------------------+
2 rows in set

What makes a good primary key

• Unique and never NULL (guaranteed).
• Immutable: you never update it.
• Short: INT, BIGINT.
• Meaningless: nothing to change in the real world.
• Ever-increasing for write-heavy tables.

What to avoid

• Business values that can change (email, phone, name).
• Long text keys.
• Composite keys of many columns on a table that other tables reference. Foreign keys would have to repeat all of them.
• Primary keys that carry information (such as 2026-07-0001 where the date is part of the key). The format will need to change one day.

Changing a primary key later

1
2
3
4

CREATE TABLE no_key (name VARCHAR(10));
INSERT INTO no_key VALUES ('a'), ('b');
ALTER TABLE no_key ADD COLUMN id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;
SELECT id, name FROM no_key;

1
2
3
4
5
6
7

+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
+----+------+
2 rows in set

On a big table, this rebuilds the whole table and takes time (Online DDL). Choose the key before the table has data.