Databases - Primary Keys

A database is an organized collection of data. To efficiently manage and retrieve data, databases use tables. Each table consists of rows (records) and columns (fields). A crucial concept in database design is the primary key.

What is a Primary Key?

A primary key is a field (or a set of fields) in a table that uniquely identifies each record in that table. It's the most important field for identifying individual entries. A primary key must satisfy the following conditions:

• Uniqueness: No two records can have the same primary key value.
• Not Null: The primary key field cannot contain a missing value (NULL).
• Immutability: The primary key value should not change over time.

Why are Primary Keys Important?

Primary keys are essential for:

• Data Integrity: Ensures that each record is uniquely identifiable.
• Efficient Data Retrieval: Allows for quick and accurate retrieval of specific records.
• Relationships between Tables: Used to link related tables together (foreign keys).

Identifying Suitable Primary Keys

When designing a database, it's important to choose appropriate primary keys. Here's a guide to help you identify suitable primary keys:

1. Natural Keys: A natural key is a field or combination of fields that already exist in the data and naturally identify a record. For example, a student's ID number or a product's serial number.
2. Artificial Keys: An artificial key is a field created specifically for the purpose of uniquely identifying records. This is often a number that is automatically generated.

Examples

Let's look at some examples to illustrate how to identify primary keys:

Example 1: Student Table

Consider a table to store information about students:

Field Name	Data Type	Primary Key?
Student ID	Integer	Yes - Natural Key (assumed to be unique)
First Name	Text	No
Last Name	Text	No
Date of Birth	Date	No
Email Address	Text	Potentially - if guaranteed unique

In this case, Student ID is the most suitable primary key because it's likely to be unique for each student and is not null.

Example 2: Product Table

Consider a table to store information about products:

Field Name	Data Type	Primary Key?
Product Code	Text	Yes - Natural Key (assumed to be unique)
Product Name	Text	No
Price	Decimal	No
Stock Quantity	Integer	No

Here, Product Code is the best primary key as it's designed to be unique for each product.

Example 3: Order Table

Consider a table to store information about orders:

Field Name	Data Type	Primary Key?
Order ID	Integer	Yes - Artificial Key (automatically generated)
Customer ID	Integer	No - Foreign Key (links to Customer Table)
Order Date	Date	No
Total Amount	Decimal	No

In this example, Order ID is the primary key. It's an artificial key, designed specifically to uniquely identify each order.

Composite Keys

Sometimes, a single field is not sufficient to uniquely identify a record. In such cases, a composite key is used. A composite key consists of two or more fields that, when combined, uniquely identify a record.

For example, in a table of student enrollments, a composite key might be (Student ID, Course ID) to ensure that a student is only enrolled in a particular course once.

Foreign Keys

A foreign key is a field in one table that refers to the primary key of another table. Foreign keys are used to establish relationships between tables. They help maintain data integrity by ensuring that relationships between tables are valid.

For instance, the Customer ID field in the Orders table would be a foreign key referencing the Customer ID (primary key) in the Customers table.