SQL from Zero › Module 2: DDL: building the structure
Primary keys
Give every row its own ID so the database can always tell rows apart.
Lesson 9 of 33 · about 10 minutes
Video: Every row needs a roll numberThe narrated video for this lesson is coming soon. The lesson below covers the same ideas.
The lesson
In Module 1 you met the primary key: the column that identifies exactly one row, like a roll number in a school register. Now you'll create one.
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT,
email TEXT
);
Adding PRIMARY KEY after a column tells the database two things:
- No two rows may share this value. If you try to add a second customer with id 5, the database refuses.
- It's the official way to find a row. Other tables will point at it later with foreign keys.
A handy SQLite feature: an INTEGER PRIMARY KEY fills itself in. If you add a customer without an id, the database picks the next number for you. (Other databases do the same with AUTO_INCREMENT or SERIAL.)
A table can only have one primary key. That's the point of it: one official ID per row.
Key ideas
- PRIMARY KEY marks the column that uniquely identifies each row.
- The database refuses duplicate primary key values.
- A table has only one primary key. In SQLite, an INTEGER PRIMARY KEY numbers new rows automatically.
Practise
Loading practice…