Foreign key linking mental model in SQL - Mini Project: Build & Apply

Foreign Key Linking Mental Model

📖 Scenario: You are building a simple database for a library. The library has books and authors. Each book is written by one author. You want to link books to their authors using a foreign key.

🎯 Goal: Create two tables: authors and books. Then link the books table to the authors table using a foreign key on author_id. This will help you understand how foreign keys connect tables in a database.

📋 What You'll Learn

Create a table called authors with columns author_id (integer, primary key) and author_name (text).

Create a table called books with columns book_id (integer, primary key), title (text), and author_id (integer).

Add a foreign key constraint on books.author_id that references authors.author_id.

Use standard SQL syntax.

💡 Why This Matters

🌍 Real World

Foreign keys help keep data connected and consistent in real databases like libraries, stores, or social networks.

💼 Career

Understanding foreign keys is essential for database design and working with relational databases in many tech jobs.

1

Create the authors table

Write a SQL statement to create a table called authors with two columns: author_id as an integer primary key, and author_name as text.

SQL

-- Create the authors table with author_id and author_name
-- Your code here

Need a hint?

Use CREATE TABLE authors and define author_id as INTEGER PRIMARY KEY.

2

Create the books table

Write a SQL statement to create a table called books with three columns: book_id as an integer primary key, title as text, and author_id as integer.

SQL

CREATE TABLE authors (
    author_id INTEGER PRIMARY KEY,
    author_name TEXT
);

-- Create the books table with book_id, title, and author_id
-- Your code here

Need a hint?

Use CREATE TABLE books and define the columns as described.

3

Add the foreign key constraint

Modify the books table creation statement to add a foreign key constraint on author_id that references authors.author_id.

SQL

CREATE TABLE authors (
    author_id INTEGER PRIMARY KEY,
    author_name TEXT
);

-- Modify the books table to add a foreign key on author_id referencing authors.author_id
CREATE TABLE books (
    book_id INTEGER PRIMARY KEY,
    title TEXT,
    author_id INTEGER
    -- Your code here
);

Need a hint?

Add FOREIGN KEY (author_id) REFERENCES authors(author_id) inside the books table definition.

4

Complete the foreign key linking

Ensure the full SQL code includes both authors and books tables with the foreign key constraint linking books.author_id to authors.author_id.

SQL

-- Verify the full SQL code for authors and books tables with foreign key
-- Your code here

Need a hint?

Make sure both tables and the foreign key constraint are present in the code.