A primary key is a field used to identify a row. Unique specifies that there cannot be any repeating values. A table can contains columns which have the following values:

  • be unique

  • have a primary key

  • be unique and have a primary key

The difference is that a unique column only contains non-repeating values, but a value does not exist for indexing to occur. A primary key column will have indexing capability, but the column may have repeating data values.

There are cases where you may want to have a unique primary key column. For instance, the inventory table contains the product identification numbers for each product offered. The product number should be unique because if two products had the same number, it is hard to decipher which product a customer was ordering. It also needs to be a primary key because this column is used to perform searches on queries and to distinguish this row from other rows. Some differences could be summed up in the table below:

Table 5-5. Primary/Unique Difference

Primary Key Unique
identifies the row alternative access to the row
difficult to update can easily be updated, if the new value is also unique
Does not allow NULL values Allows NULL values

An index or primary key functionality is provided to aid the system to become more efficient. Some queries may require an index or primary key to perform a faster search, but others may just be as fast using a sequential search. As the database administrator or manager, you may need to anticipate for these situations.