Databases - Primary Keys

A database is a structured 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 column (or a set of columns) in a table that uniquely identifies each record in that table. It ensures that no two records are exactly the same. A primary key must:

• Uniquely identify each record.
• Not contain any NULL values.
• Be immutable (its value cannot be changed).

Why are Primary Keys Important?

Primary keys are essential for:

• Data Retrieval: They allow for quick and efficient retrieval of specific records.
• Data Integrity: They enforce uniqueness and prevent duplicate records.
• Relationships: They are used to establish relationships between tables (foreign keys).

Identifying Suitable Primary Keys

When designing a database, you need to carefully choose primary keys. Here's a guide to identifying suitable primary keys:

1. Look for natural keys: A natural key is a column (or combination of columns) that already has a meaning in the real world and can uniquely identify a record.
2. Consider surrogate keys: If no suitable natural key exists, you can create a surrogate key – an artificial key that has no inherent meaning but guarantees uniqueness. This is often an auto-incrementing integer.

Examples

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

Example 1: Students Table

Consider a table called Students with the following columns:

Student ID	First Name	Last Name	Date of Birth
101	Alice	Smith	2002-03-15
102	Bob	Johnson	2001-11-20
103	Charlie	Brown	2002-07-01

Suitable Primary Key: Student ID. It's a unique identifier for each student.

Example 2: Products Table

Consider a table called Products with the following columns:

Product Code	Product Name	Price	Quantity in Stock
P100	Laptop	1200	10
P101	Mouse	25	50
P102	Keyboard	75	30

Suitable Primary Key: Product Code. It's a unique identifier for each product.

Example 3: Library Books Table

Consider a table called Library Books with the following columns:

Book Title	Author	ISBN	Publication Year
The Hitchhiker's Guide to the Galaxy	Douglas Adams	978-0345391803	1979
Pride and Prejudice	Jane Austen	978-0141439518	1813
1984	George Orwell	978-0451524935	1949

Suitable Primary Key: ISBN. It's a unique identifier for each book.

Example 4: No obvious natural key

Consider a table called Employees with the following columns:

Employee Name	Department	Salary
John Doe	Sales	50000
Jane Smith	Marketing	60000
Peter Jones	Sales	55000

Suitable Primary Key: Employee ID (a surrogate key, e.g., auto-incrementing integer). Since employee names might not be unique, an artificial ID is needed.

Foreign Keys

Primary keys are often used to establish relationships between tables. A foreign key in one table refers to the primary key in another table. This allows you to link related data across different tables.

Example: A Orders table might have a foreign key referencing the Students table's primary key (Student ID) to indicate which student placed the order.