The Basics of Relational Databases: Primary Keys and Foreign Keys Explained

What are Keys in Relational Databases?
In a relational database, keys are crucial for ensuring data integrity and establishing relationships between tables. The two most important types of keys are:

• Primary Key: A unique identifier for each record in a table.
• Foreign Key: A field in one table that references the primary key in another table.

Primary Key

• Ensures each row in a table is unique.
• Can’t contain NULL values.

• Examples:

  • ID column in a users table.
  • OrderID in an orders table.

Code Example: Creating a Primary Key

CREATE TABLE users (
    ID INT PRIMARY KEY,
    Name VARCHAR(50),
    Email VARCHAR(100)
);

Foreign Key

• Establishes a link between two tables.
• Enforces referential integrity by ensuring the referenced record exists. Code Example: Creating a Foreign Key Here’s how you link the orders table to the users table using a foreign key:

CREATE TABLE orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATE,
    UserID INT,
    FOREIGN KEY (UserID) REFERENCES users(ID)
);

Relationships in Action
Consider these tables:

users Table:

ID	Name	Email
1	Alice	[e-mail]
2	Bob	[e-mail]

orders Table:

OrderID	OrderDate	UserID
101	2025-01-10	1
102	2025-01-11	2

Query: Retrieve all orders along with user information.

SELECT orders.OrderID, orders.OrderDate, users.Name, users.Email
FROM orders
JOIN users ON orders.UserID = users.ID;

Why Keys Matter
Without keys, it’s easy to end up with duplicate or inconsistent data. For example:

• Orders with no valid UserID would exist.
• Duplicate user records might clutter the database.

Challenge: Design Your Own Keys
Scenario: You’re building a database for a library system.

• What would you use as a primary key for books?
• How would you design a foreign key to track which user has borrowed a specific book?

Think About It

• Why is it important to have a primary key in every table?
• Can a table have multiple foreign keys? Why or why not?