Lesson 3 / الدرس 3

A key has one job, and meaning is not it / للمفتاح عمل واحد، وليس المعنى منه

A primary key identifies a row for as long as the row exists. Anything with meaning can change, and a key that changes takes every row pointing at it along with it — which is why the best keys say nothing at all.

المفتاح الأساسي يعرّف صفًّا ما دام الصف موجودًا. وكل ما له معنى يمكن أن يتغير، والمفتاح الذي يتغير يأخذ معه كل صف يشير إليه — ولهذا فأفضل المفاتيح ما لا يقول شيئًا البتة.

An email address looks like a perfect key: every guest has one and no two share it. Then somebody changes theirs. Every booking, payment and message that pointed at the old address now points at nothing, and the fix is an update across every table that referenced it — assuming you can still find them all.

Natural and surrogate

A natural key is data that already identifies the row — an email, a national ID, a room number. Its weakness is that it means something, and things that mean something get corrected.
A surrogate key means nothing on purpose — an auto-increment integer or a UUID, invented by the database and never shown to anyone. Nothing about the guest can make it wrong, because it never claimed anything about the guest.
Use a surrogate as the primary key, and keep the natural one as a UNIQUE column. You get a stable identity for the joins and the database still refuses two guests with one email. This is not a compromise; it is both jobs done by the thing suited to each.

CREATE TABLE guests (
    id     INT AUTO_INCREMENT PRIMARY KEY,   -- means nothing, never changes
    email  VARCHAR(190) NOT NULL UNIQUE,     -- means something, may change
    name   VARCHAR(120) NOT NULL
);

-- Everything else points at the thing that cannot change.
CREATE TABLE bookings (
    id       INT AUTO_INCREMENT PRIMARY KEY,
    guest_id INT NOT NULL,
    FOREIGN KEY (guest_id) REFERENCES guests(id)
);

-- A guest changing their email is now one UPDATE of one row.
UPDATE guests SET email = 'new@example.com' WHERE id = 3;

That last line is the whole argument. With a surrogate key, changing an email is one row; with a natural key it is every table that ever referenced the guest. The 190 on the email column is not arbitrary either — it is about the longest a VARCHAR can be and still fit in a MySQL index under utf8mb4.

Counting up, or a random string

	AUTO_INCREMENT	UUID
Size	4 or 8 bytes	16 bytes, or 36 as text — and it is in every index
Ordering	New rows land at the end of the index	Random values scatter writes across it
Guessable	Yes — /booking/41 tells a visitor there is a 40	No
Made where	By the database, on insert	Anywhere, before the row exists

The guessable row matters more than it looks: a sequential id in a URL tells anyone how many you have and lets them try the next one — which is exactly the missing-permission-check failure the uploads lesson described. The answer is to check permission, not to hide the number; but a UUID does remove the invitation.

Check yourself / اختبر نفسك

1. Why is an email address a poor primary key?

2. What does a sequential id in a URL reveal?

3. When does a composite primary key stop being a good idea?