PRIMARY KEY vs UNIQUE vs INDEX

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

  NULL

  values.

Most often it is the

id

column. It uniquely points to a row, like a scroll's catalog number.

1SELECT * 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

  NULL

  values (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.