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:

orders Table:

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?