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