Primary Keys: Giving Every Row a Unique Identity

Two different users could easily share the same username by coincidence, or the same first name, or even the same email typo. So how does a database ever tell two rows apart with total certainty? The answer is a primary key.

A primary key is a column, almost always named id, whose value is guaranteed to be unique for every single row in that table, and is never left empty. No two rows can ever share the same primary key value.

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50),
  email VARCHAR(100)
);

AUTO_INCREMENT does the counting for you

AUTO_INCREMENT tells the database to automatically hand out the next whole number, 1, then 2, then 3, and so on, every time a new row is inserted. That is why your earlier INSERT statements never had to mention id at all, the database was quietly assigning it for you.

Why this matters in practice

Usernames can be renamed, emails can change, but a row's id never changes and never repeats. That makes id the one thing you can always trust to point at exactly one specific row:

UPDATE users SET email = 'new@example.com' WHERE id = 5;
DELETE FROM comments WHERE id = 42;

In both of these familiar examples from the previous lesson, id is what makes the WHERE clause completely unambiguous. There is exactly one row with id equal to 5, never more, never less.

A hint of what is coming next

Primary keys have one more superpower: they are how one table can point at a row in a completely different table. A post can store the id of the user who wrote it, letting you connect a posts table to a users table. That idea, called a foreign key, is exactly what makes the next lesson on JOIN possible.