Primary keys and AUTO_INCREMENT

What a primary key is

A primary key is the column (or columns) that identifies each row. It guarantees two things: the value is unique (no two rows share it) and never NULL. Everything else in the database refers to a row through its key: orders store customer_id, which is the primary key of customers. Without a reliable key, you could not update or delete exactly one row, or link tables safely.

A table can have only one primary key, though that key can span several columns.

Defining a primary key

There are two equivalent ways. Inline, next to the column:

CREATE DATABASE IF NOT EXISTS practice;
USE practice;
DROP TABLE IF EXISTS tags;
CREATE TABLE tags (
  id   INT PRIMARY KEY,
  name VARCHAR(40) NOT NULL
);

Or as a table-level clause, which is needed for composite keys:

DROP TABLE IF EXISTS tags2;
CREATE TABLE tags2 (
  id   INT,
  name VARCHAR(40) NOT NULL,
  PRIMARY KEY (id)
);

MySQL builds an index on the key automatically, so lookups by key are very fast. Duplicates are refused:

INSERT INTO tags VALUES (1, 'sql'), (2, 'php');

INSERT INTO tags VALUES (1, 'duplicate id');

ERROR 1062 (23000): Duplicate entry '1' for key 'tags.PRIMARY'

AUTO_INCREMENT

Typing ids by hand is error-prone. Mark an integer key AUTO_INCREMENT and MySQL assigns the next number when you leave the column out or give NULL:

DROP TABLE IF EXISTS notes;
CREATE TABLE notes (
  id   INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  body VARCHAR(100) NOT NULL
);
INSERT INTO notes (body) VALUES ('first'), ('second');
INSERT INTO notes (body) VALUES ('third');
SELECT LAST_INSERT_ID() AS last_id;
SELECT * FROM notes;

+---------+
| last_id |
+---------+
|       3 |
+---------+
+----+--------+
| id | body   |
+----+--------+
|  1 | first  |
|  2 | second |
|  3 | third  |
+----+--------+

LAST_INSERT_ID() returns the first id generated by the most recent insert in your own connection. When a multi-row insert generates several ids, it returns the first of them, so be careful with it after multi-row inserts.

Some facts you should know:

• The counter only goes forward. Delete the last row and the next insert gets a new, higher number.
• Failed inserts and rolled-back transactions also consume numbers, so gaps are normal. Never use ids as a count of rows, and do not try to renumber them.
• A table can have only one AUTO_INCREMENT column, and it must be indexed (normally the primary key).
• TRUNCATE TABLE resets the counter. You can also set it with ALTER TABLE notes AUTO_INCREMENT = 1000; to start at a particular number.

DELETE FROM notes WHERE id = 3;
INSERT INTO notes (body) VALUES ('fourth');
SELECT * FROM notes;

+----+--------+
| id | body   |
+----+--------+
|  1 | first  |
|  2 | second |
|  4 | fourth |
+----+--------+

The new row has id 4, not 3. Id 3 is gone for good.

Natural versus surrogate keys

A natural key is real data that is unique, such as an email address, ISBN or national id. A surrogate key is an artificial number added only to identify the row, such as AUTO_INCREMENT. Natural keys sound attractive, but real data changes: people change email addresses, and standards get reissued. If the key changes, every table that references it must change too. A surrogate key never changes and is small and fast.

A good pattern is a surrogate primary key plus a UNIQUE constraint on the natural identifier, which the next lesson covers. The sample shop does this: customers.id is the key, and email is unique.

Large systems sometimes use UUIDs as keys, so ids can be generated anywhere without coordination. They are bigger and, if random, slower to insert in the index. If you need one, the UUID Generator shows what they look like.

Composite primary keys

When no single column identifies a row but a combination does, use a composite key. A student enrols in a course once, so the pair is unique:

DROP TABLE IF EXISTS enrolments;
CREATE TABLE enrolments (
  student_id INT NOT NULL,
  course_id  INT NOT NULL,
  enrolled_on DATE NOT NULL,
  PRIMARY KEY (student_id, course_id)
);
INSERT INTO enrolments VALUES (1, 10, '2024-01-10'), (1, 11, '2024-01-10'), (2, 10, '2024-01-12');

INSERT INTO enrolments VALUES (1, 10, '2024-02-01');

ERROR 1062 (23000): Duplicate entry '1-10' for key 'enrolments.PRIMARY'

Student 1 may appear many times, and course 10 may appear many times, but not the same pair twice. Order matters for speed: this index serves lookups by student_id alone, or by both columns, but not efficiently by course_id alone.

Common mistakes

• No primary key at all. Updates and replication become unreliable.
• Using an email or name as the key.
• Treating AUTO_INCREMENT ids as gap-free or as a row count.
• Choosing INT for a table that will exceed 2 billion rows (use BIGINT UNSIGNED).
• Reading LAST_INSERT_ID() after a multi-row insert and assuming it is the last id.

Exercise

Create a table vehicles with an auto-incrementing id, a required plate (up to 15 characters), and a make. Insert three rows, delete the middle one, insert another, and check the ids. Then explain why you should not rely on the ids being consecutive.

DROP TABLE IF EXISTS vehicles;
CREATE TABLE vehicles (id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, plate VARCHAR(15) NOT NULL, make VARCHAR(40));
INSERT INTO vehicles (plate, make) VALUES ('AB-01', 'Tata'), ('AB-02', 'Honda'), ('AB-03', 'Ford');
DELETE FROM vehicles WHERE id = 2;
INSERT INTO vehicles (plate, make) VALUES ('AB-04', 'Kia');
SELECT * FROM vehicles;

+----+-------+------+
| id | plate | make |
+----+-------+------+
|  1 | AB-01 | Tata |
|  3 | AB-03 | Ford |
|  4 | AB-04 | Kia  |
+----+-------+------+