Every row needs an identity.
Direct answer
A primary key is a column, or combination of columns, that uniquely identifies every row in a table. It must be unique and not NULL. Most tables use an artificial ID, called a surrogate key, such as an auto-incrementing number.
Example
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT UNIQUE
);
Natural vs surrogate
| Natural key | Surrogate key | |
|---|---|---|
| Example | email, passport number | id number, UUID |
| Pros | meaningful | stable, simple |
| Cons | can change | no meaning on its own |
Good rules
- Keep it unique and never NULL.
- Do not change it after creation.
- Prefer simple keys.
Where Teyro fits
Teyro's SQL and Databases for Beginners course turns this into short lessons you can play in a few minutes a day. It has 50 lessons, and you can start free.
The bottom line
A stable ID per row makes relationships and updates safe. See foreign keys.
