SQL and MySQL course Β· Module 9: Indexes and normalization
PRIMARY KEY vs UNIQUE vs INDEX
In this lesson4
In the Library you will meet three kinds of catalog. They look similar but differ in the strictness of their rules. Let us learn them from the strongest.
PRIMARY KEY (primary key)
This is the strictest catalog - the main catalog number of every scroll. Its rules:
- there is exactly one per table,
- its values are unique (no duplicates),
- it does not allow
NULLvalues.
Most often it is the id column. It uniquely points to a row, like a scroll's catalog number.
SELECT * FROM autorzy WHERE id = 3;UNIQUE INDEX
A little looser. It also guards uniqueness, but:
- there can be many of them in one table,
- it allows
NULLvalues (in MySQL there can be many of them).
Ideal for columns like email - every address different, but the table already has its primary key elsewhere.
Plain INDEX
The gentlest catalog. It does not guard uniqueness - it allows repetitions. Its only job is to speed up searching.
It works great on the autor_id column in the ksiazki table - one author can have many books, so the values repeat, but we want to find them quickly.
Summary of differences
| Kind | Unique? | NULL? | How many per table |
|---|---|---|---|
| PRIMARY KEY | yes | no | one |
| UNIQUE | yes | yes | many |
| INDEX | no | yes | many |
Remember: all three speed up reading, but only the first two enforce uniqueness.
Spotted a mistake in this lesson?
Check yourself
Answer the questions from this lesson. Pick an answer to see right away whether it is correct.
1. How does PRIMARY KEY differ from a UNIQUE INDEX?
2. What sets a plain INDEX apart from PRIMARY KEY and UNIQUE?
These are 2 of 3 questions for this lesson. Solve the rest in the game.
Hands-on tasks in the game
- Vertical ordering
Arrange the three kinds of indexes from the strongest to the weakest constraint (from the strictest uniqueness control to none):
- Code editor
Create a plain index named idx_autor on the autor_id column in the ksiazki table.
- Click in order
Click the kinds of indexes in order from the strictest to the gentlest constraint: