Primary Key

Learning Outcomes

  • Discuss the importance of primary key uniqueness
  • Choose a primary key

Every table must have a primary key—a field with a unique value for each record. Otherwise there would be no way to distinguish between records. A primary key consists of one or more fields that uniquely identify each record that you store in the table. Often, there is a unique identification number, such as an ID number, a serial number, or a code, that serves as a primary key.

For example, you might have a Customers table where each customer has a unique customer ID number. The customer ID field is the primary key of the Customers table. When a primary key contains more than one field, it is usually composed of pre-existing fields that, taken together, provide unique values. For example, you might use a combination of last name, first name, and birth date as the primary key for a table about people.

Choosing a Primary Key

Without the ProductID field, another field would be needed to uniquely identify each product in the database. Without a strong primary key, the database engine would be at a loss to perform basic operations on the table properly.

Good primary keys must:

  • Uniquely identify each record,
  • Not be null,
  • Exist when the record is created,
  • Must remain stable,
  • Be simple and contain as few attributes as possible.

Practice Questions