When should I use primary key or index?

sql primary-key indexing
Sein Kraft picture Sein Kraft · May 21, 2010 · Viewed 46.6k times · Source

When should I use a primary key or an index?

What are their differences and which is the best?

Answer

Michael Borgwardt picture Michael Borgwardt · May 21, 2010

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.

Related questions

SQL: set existing column as Primary Key in MySQL

I have a database with 3 columns: id, name, somethingelse This table has no index set and i am getting "No index defined!" in phpmyadmin id is a 7digit alphanumeric value, unique to each row. I want to set Drugid to …

Read More
sql primary key and index

Say I have an ID row (int) in a database set as the primary key. If I query off the ID often do I also need to index it? Or does it being a primary key mean it's already indexed? …

Read More
Indexes and multi column primary keys

Went searching and didn't find the answer to this specific noob question. My apologies if I missed it. In a MySQL database I have a table with the following primary key PRIMARY KEY id (invoice, item) In my application I …

Read More