Primary keys and AUTO_INCREMENT

A primary key is the column that identifies exactly one row. It must be unique and it can never be NULL. Every table you design in this course gets one, because without it there is no reliable way to update or delete a specific row, and no way for other tables to reference it.

A common primary key looks like this:

id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY

AUTO_INCREMENT tells MySQL to allocate the value itself: each insert that omits id receives the next number. You never generate identifiers in application code, and two concurrent inserts never collide.

Insert a row and read back the identifier the server chose:

INSERT INTO notes (title) VALUES ('Plan the week');
SELECT LAST_INSERT_ID();

LAST_INSERT_ID() returns the value generated for your connection, unaffected by inserts from other sessions. Application drivers expose the same number — in mysql2 it arrives as result.insertId — so you can insert a note and immediately use its id for related rows.

The database enforces uniqueness for you. Insert an explicit id that already exists and MySQL refuses:

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

That rejection is the primary key doing its job. Fix the insert, not the constraint.

Identifiers, not information

Two habits keep auto-increment values trouble-free.

First, expect gaps. A rolled-back transaction or a deleted row leaves holes in the sequence, and MySQL does not reuse them. Gaps are normal and harmless. Code that assumes MAX(id) equals the row count, or that ids form an unbroken series, is wrong.

Second, the value identifies a row inside the database and means nothing more. Do not expose assumptions about consecutive identifiers to application behavior or authorization. “The user can see note 42 because they asked for note 42” is a security hole — check ownership in the query, and treat the id as an opaque handle.