Primary Keys vs Indexes

16 Jan 2013

What are the differences between a primary key and a index in database systems?

Primary key is a logical object, meaning that it simply defines a set of properties on one or a set of columns. The properties are usually constraints about the uniqueness of all the columns which make up the primary key and non-null values within primary key columns.

This uniqueness property makes primary key useful for identifying a single row in a table every time. In most if not all RDBMs, primary key has an index created on it. Therefore having primary key in most RDBMs is the same as having an index from an indexing or query performance point of view.

An index on the other hand doesn’t define uniqueness (however in certain RDBMs they also have something like CREATE UNIQUE INDEX). An index is used to find rows in the table more quickly based on the values which are part of the index. When you create an index within the database, you are creating a physical object which will be saved onto disk.