IGCSE ICT 0417 - Databases

Topic: Primary and Foreign Keys

This section covers the fundamental concepts of primary and foreign keys in database design. Understanding these keys is crucial for creating well-structured and efficient databases.

Primary Keys

A primary key is a column (or a set of columns) in a database table that uniquely identifies each record (row) in that table. It ensures that no two rows are exactly alike.

Key characteristics of a primary key:

• Uniqueness: Each value in the primary key column must be unique.
• Not Null: A primary key column cannot contain null values.
• Immutability: The value of a primary key should not be changed after it's been assigned.

Examples of primary keys:

• Student ID in a student table
• Product Code in a product table
• Order ID in an order table

Foreign Keys

A foreign key is a column (or set of columns) in one table that refers to the primary key of another table. It establishes a link between the two tables.

Key characteristics of a foreign key:

• References a Primary Key: The foreign key column must reference a primary key in another table.
• Can Contain Nulls: A foreign key column can contain null values, indicating that there is no related record in the other table.
• Enforces Referential Integrity: The database management system (DBMS) uses foreign keys to enforce referential integrity, ensuring that relationships between tables remain consistent. This prevents orphaned records.

Example:

Consider a Orders table and a Customers table. The Orders table might have a CustomerID column. This CustomerID would be a foreign key referencing the CustomerID (primary key) in the Customers table.

Creating Primary and Foreign Keys (Conceptual Example)

Let's illustrate with a simple example of a Customers and Orders database schema.

Table	Column	Data Type	Key Type	Description
Customers	CustomerID	INT	Primary Key	Unique identifier for each customer.
Customers	FirstName	VARCHAR(50)		Customer's first name.
Customers	LastName	VARCHAR(50)		Customer's last name.
Orders	OrderID	INT	Primary Key	Unique identifier for each order.
Orders	CustomerID	INT	Foreign Key referencing Customers.CustomerID	Links the order to the customer who placed it.
Orders	OrderDate	DATE		Date the order was placed.

Relationships between Tables

Foreign keys are used to define relationships between tables. The type of relationship depends on the database design:

• One-to-Many: One record in the primary key table can be related to multiple records in the foreign key table. (e.g., One customer can place many orders).
• One-to-One: One record in the primary key table is related to exactly one record in the foreign key table.
• Many-to-Many: Multiple records in the primary key table can be related to multiple records in the foreign key table. This is typically implemented using a junction table.

Referential Integrity

Referential integrity is a constraint that ensures the relationships between tables remain consistent. The DBMS enforces this by preventing actions that would violate the relationships, such as:

• Deleting a record in the primary key table if there are related records in the foreign key table.
• Updating a primary key value if it is referenced by foreign keys in other tables.

The specific behavior when referential integrity is violated can be configured within the database system (e.g., cascade delete, set null).

Summary

Primary and foreign keys are essential components of relational database design. They ensure data integrity, facilitate relationships between tables, and enable efficient data retrieval.