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 [email protected]
2 Bob [email protected]

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?