When should I use primary key or index?

indexing, primary-key, sql

Solution

Basically, a primary key is (at the implementation level) a special kind of index. Specifically:

- A table can have only one primary key, and with very few exceptions, every table should have one.

- A primary key is implicitly `UNIQUE` - you cannot have more than one row with the same primary key, since its purpose is to uniquely identify rows.

- A primary key can never be `NULL`, so the row(s) it consists of must be NOT NULL

A table can have multiple indexes, and indexes are not necessarily `UNIQUE`. Indexes exist for two reasons:

- To enforce a uniquness constraint (these can be created implicitly when you declare a column UNIQUE)

- To improve performance. Comparisons for equality or "greater/smaller than" in WHERE clauses, as well as JOINs, are much faster on columns that have an index. But note that each index decreases update/insert/delete performance, so you should only have them where they're actually needed.

Problem

Original source