DataslopePrimary Keys
Learn how SQLite uses primary keys to give every row a stable unique identity.
Imagine two people have the same name. If your table only stores names, how can SQLite tell them apart?
That is the job of a primary key: it gives each row a reliable identity.
The identity problem
Two rows can look almost the same:
Names can repeat. Addresses can repeat. Phone numbers can change. A table needs one value that means: this exact row, not any other row.
What a primary key is
A primary key is a column, or sometimes a combination of columns, that uniquely identifies each row in a table.
For beginners, the most common pattern in SQLite is a column named id:
Now the two John Smith rows are not confused:
- Customer
1is one person. - Customer
2is another person.
The id value is the row's identity inside this table.
The two promises of a primary key
A primary key gives two important guarantees:
- Unique: no two rows may have the same primary key value.
- Not null: every row must have an identity value.
Those promises make the key safe to use when you need to point to a specific row.
Why is a person's name usually a poor primary key?
Names cannot be stored as text in SQLite.
Names can repeat, change, or be missing.
Names are always longer than numbers.
SQLite only allows primary keys on price columns.
Natural keys and surrogate keys
There are two common ways to choose a primary key.
A natural key is a real-world value that is already unique, such as a country code.
A surrogate key is a made-up value used only to identify the row, such as id = 1.
Surrogate keys are very common because they are simple and stable. The number does not have to mean anything. Its job is only to identify the row.
SQLite's special INTEGER PRIMARY KEY
In SQLite, this declaration is special:
id INTEGER PRIMARY KEYIt makes id an alias for SQLite's internal rowid. In plain language: SQLite can automatically assign a new whole-number id when you insert a row and leave id out.
No SERIAL in SQLite
SQLite does not use PostgreSQL's SERIAL type. For the usual beginner auto-id pattern, write id INTEGER PRIMARY KEY.
See it in SQLite
Run this example. Notice that the INSERT statements do not provide ids. SQLite fills them in.
You should see ids like 1, 2, and 3. Those ids are stable row identities you can use in other tables later.
Primary keys help tables connect
Primary keys are especially important when one table refers to another.
The orders.customer_id value points to a customer's id. Without primary keys, that link would be unreliable.
Check your understanding
What is the main purpose of a primary key?
To make a table display in rainbow colors.
To uniquely identify each row in a table.
To force every value in the table to be text.
To delete duplicate rows automatically.
Which SQLite column definition is the usual beginner pattern for an automatically assigned id?
id SERIAL PRIMARY KEY
id INTEGER PRIMARY KEY
id TEXT ALWAYS NUMBER
id BOOLEAN PRIMARY KEY
What two rules does a primary key provide?
It must be alphabetic and lowercase.
It must be sorted and visible.
It must be unique and not null.
It must contain the row's full description.
A surrogate key is best described as what?
A real-world value like a person's current phone number.
A made-up identifier, often an id number, created only to identify a row.
A column that stores several names in one cell.
A query that sorts a table.