Engineering Fluency OS

Why This Matters

A single table can only get you so far. Real applications have users who place orders, orders that contain products, and products that belong to categories. How do you connect these tables together? The answer is primary keys and foreign keys. A primary key uniquely identifies each row, and a foreign key is a reference from one table to another.

Without keys, your data is a disconnected mess. With them, you can answer questions like "What did this customer order last month?" or "Which products are running low?" by joining tables together. Keys are the glue that makes relational databases relational.

Define Terms

Visual Model

Usersid | name | email

PK: idunique, not null

Ordersid | user_id | total

FK: user_idreferences users.id

1 : None-to-many

The full process at a glance. Click Start tour to walk through each step.

Primary keys identify rows. Foreign keys link tables. The relationship arrow shows how they connect.

Code Example

Code

-- SQL: Creating related tables with keys
CREATE TABLE users (
  id    INTEGER PRIMARY KEY,
  name  TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL
);

CREATE TABLE orders (
  id        INTEGER PRIMARY KEY,
  user_id   INTEGER NOT NULL,
  total     DECIMAL(10,2),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Insert sample data
INSERT INTO users VALUES (1, Alice, alice@test.com);
INSERT INTO users VALUES (2, Bob, bob@test.com);
INSERT INTO orders VALUES (1, 1, 59.99, 2024-01-15);
INSERT INTO orders VALUES (2, 1, 24.50, 2024-01-20);
INSERT INTO orders VALUES (3, 2, 149.00, 2024-01-18);

-- JOIN: combine users and orders
SELECT users.name, orders.total, orders.created_at
FROM orders
JOIN users ON orders.user_id = users.id;
-- Alice | 59.99  | 2024-01-15
-- Alice | 24.50  | 2024-01-20
-- Bob   | 149.00 | 2024-01-18

-- LEFT JOIN: include users even if they have no orders
SELECT users.name, COUNT(orders.id) AS order_count
FROM users
LEFT JOIN orders ON users.id = orders.user_id
GROUP BY users.name;
-- Alice | 2
-- Bob   | 1

-- Foreign key prevents invalid references
INSERT INTO orders VALUES (4, 999, 10.00, 2024-01-25);
-- ERROR: FOREIGN KEY constraint failed (user 999 does not exist)

Interactive Experiment

Try these exercises:

Create a students table and a grades table with a foreign key relationship. Insert data and JOIN them.
Try inserting a grade with a student_id that does not exist. What happens with and without a FOREIGN KEY constraint?
Create a many-to-many relationship: students, courses, and a enrollments junction table. Query which students are in each course.
Use LEFT JOIN vs INNER JOIN and observe the difference when some students have no grades.

Quick Quiz

Coding Challenge

Simulate a JOIN in Code

Write a function called `innerJoin` that takes two arrays of objects, a key from the left array, and a key from the right array. It should return a new array of merged objects where the keys match -- simulating a SQL INNER JOIN.

Loading editor...

Real-World Usage

Keys and relationships are everywhere in production:

User authentication: A sessions table has a foreign key to users. When you log in, the app creates a session row linked to your user.
E-commerce: Products, orders, order line items, and categories form a web of foreign key relationships.
Social media: Followers are a many-to-many relationship between users (a junction table with follower_id and followed_id).
Content management: Posts belong to authors (one-to-many), posts have tags (many-to-many via a junction table).
Data integrity: Foreign keys prevent orphaned records -- you cannot delete a user who still has orders without handling the orders first.

Tables & Keys