58

Can anyone tell me what is the difference between a primary key and index key. And when to use which?

TylerH
  • 20,799
  • 66
  • 75
  • 101
pavan
  • 979
  • 3
  • 11
  • 21

5 Answers5

77

A primary key is a special kind of index in that:

  • there can be only one;
  • it cannot be nullable; and
  • it must be unique.

You tend to use the primary key as the most natural unique identifier for a row (such as social security number, employee ID and so forth, although there is a school of thought that you should always use an artificial surrogate key for this).

Indexes, on the other hand, can be used for fast retrieval based on other columns. For example, an employee database may have your employee number as the primary key but it may also have an index on your last name or your department.

Both of these indexes (last name and department) would disallow NULLs (probably) and allow duplicates (almost certainly), and they would be useful to speed up queries looking for anyone with (for example) the last name 'Corleone' or working in the 'HitMan' department.

paxdiablo
  • 854,327
  • 234
  • 1,573
  • 1,953
  • 15
    You should mention that a primary key is always indexed, meaning that a primary key is also always an index key. – Gabe Mar 21 '11 at 07:54
  • 2
    @Gabe, I _think_ that's covered just be stating that the primary key is a kind of index. Stating that an index is indexed seems a little superfluous to me, but if you can come up with a workable change (I couldn't but that may just be a limitation of mine), I'll welcome the input. – paxdiablo Mar 21 '11 at 08:01
  • 23
    A key is not a kind of index! A key (minimal superkey) is a set of attributes, the values of which are unique for every tuple. An index is a performance optimisation feature that enables data to be accessed faster. – nvogel Mar 21 '11 at 10:25
  • OK, maybe "A primary key is always indexed, but is special as compared to an index key in that:"? – Gabe Mar 21 '11 at 14:14
  • 3
    @Gabe There's no fundamental reason why a primary key MUST always be indexed, although it's usually a good idea to. Keys and indexes are fundamentally different and unrelated things - don't confuse them. – nvogel Mar 22 '11 at 11:07
  • I think if you want to enforce uniqueness on the primary key you will have to have it indexed. Uniqueness in the data column is enforced via uniqueness in indexing behind the scenes – figs_and_nuts Jan 31 '22 at 00:12
28

A key (minimal superkey) is a set of attributes, the values of which are unique for every tuple (every row in the table at some point in time).

An index is a performance optimisation feature that enables data to be accessed faster.

Keys are frequently good candidates for indexing and some DBMSs automatically create indexes for keys, but that doesn't have to be so.

The phrase "index key" mixes these two quite different words and might be best avoided if you want to avoid any confusion. "Index key" is sometimes used to mean "the set of attributes in an index". However the set of attributes in question are not necessarily a key because they may not be unique.

nvogel
  • 24,981
  • 1
  • 44
  • 82
4

Oracle Database enforces a UNIQUE key or PRIMARY KEY integrity constraint on a table by creating a unique index on the unique key or primary key. This index is automatically created by the database when the constraint is enabled.

You can create indexes explicitly (outside of integrity constraints) using the SQL statement CREATE INDEX .

Indexes can be unique or non-unique. Unique indexes guarantee that no two rows of a table have duplicate values in the key column (or columns). Non-unique indexes do not impose this restriction on the column values.

Use the CREATE UNIQUE INDEX statement to create a unique index.

Specifying the Index Associated with a Constraint

If you require more explicit control over the indexes associated with UNIQUE and PRIMARY KEY constraints, the database lets you:

1. Specify an existing index that the database is to use 
   to enforce the constraint
2. Specify a CREATE INDEX statement that the database is to use to create 
   the index and enforce the constraint

These options are specified using the USING INDEX clause.

Example:

 CREATE TABLE a (
 a1 INT PRIMARY KEY USING INDEX (create index ai on a (a1)));

http://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes003.htm

Kanagavelu Sugumar
  • 18,766
  • 20
  • 94
  • 101
1

Other responses are defining the Primary Key, but not the Primary Index.

A Primary Index isn't an index on the Primary Key.

A Primary Index is your table's data structure, but only if your data structure is ordered by the Primary Key, thus allowing efficient lookups without a requiring a separate data structure to look up records by the Primary Key.

All databases (that I'm aware of) have a Primary Key.

Not all databases have a Primary Index. Most of those that don't build a secondary index on the Primary Key by default.

Will Cross
  • 111
  • 3
0

so basically create and index if you think some other columns other than your primary key will be or could be used to access your data.

stingMantis
  • 300
  • 3
  • 10