Unit 7 · Data that lasts
A primary key is not a row number
A primary key, usually id, is a label that belongs to one record forever. It is not the row’s position: delete customer 2 and customer 3 is still 3. Other tables rely on that.
The mix-up
What people often think
“The id is just the row's position in the list.”
What the tutor says
The id is a label that belongs to the customer forever. Delete customer 2 and customer 3 is still 3. Other tables rely on that.
What would break if the ids shifted every time someone was deleted?
How it works
Because ids never repeat, you cannot reuse one for a new customer. Leave the id out of your INSERT and the database picks the next free one.
Rows go across and each row is one record; columns go down and each column is one fact every record has, like city.
An example
INSERT INTO customers (name, city) VALUES ('Sam', 'Ottawa');
-- the database assigns the idClose cousins of this mix-up
The tutor watches for these in the same lesson.
“I have to pick an id for the new customer myself.”
Id 1 already belongs to Priya, and a primary key can never repeat. Leave the id out and the database picks the next free one.
“A column is one customer and a row is one kind of fact.”
Rows go across and each one is a single record. Columns go down and each one is a single fact, like city, that every record has.
Practise it with a tutor watching
Tables and rows
Read a database table and write SELECT, INSERT and a safe UPDATE in SQL.
Free, no account needed.