When should I use a primary key or an index?
What are their differences and which is the best?
Advertisement
Answer
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.