FOREIGN KEY constraint in SQL - Mini Project: Build & Apply

Creating and Using FOREIGN KEY Constraint in SQL

📖 Scenario: You are building a simple database for a small bookstore. You want to keep track of books and their authors. Each book must be linked to an author to ensure data consistency.

🎯 Goal: Create two tables: Authors and Books. Use a FOREIGN KEY constraint in the Books table to link each book to an author in the Authors table.

📋 What You'll Learn

Create an Authors table with columns AuthorID (primary key) and Name.

Create a Books table with columns BookID (primary key), Title, and AuthorID.

Add a FOREIGN KEY constraint on AuthorID in the Books table referencing AuthorID in the Authors table.

💡 Why This Matters

🌍 Real World

Bookstores and many other businesses use foreign keys to keep related data connected and consistent.

💼 Career

Understanding foreign keys is essential for database design and ensuring data integrity in real-world applications.

1

Create the Authors table

Write a SQL statement to create a table called Authors with two columns: AuthorID as an integer primary key, and Name as a text field.

SQL

-- Create the Authors table with AuthorID and Name
-- Your code here

Need a hint?

Use CREATE TABLE Authors and define AuthorID as INT PRIMARY KEY.

2

Create the Books table without foreign key

Write a SQL statement to create a table called Books with three columns: BookID as an integer primary key, Title as a text field, and AuthorID as an integer.

SQL

CREATE TABLE Authors (
    AuthorID INT PRIMARY KEY,
    Name TEXT
);
-- Create the Books table with BookID, Title, and AuthorID
-- Your code here

Need a hint?

Define BookID as INT PRIMARY KEY, Title as TEXT, and AuthorID as INT.

3

Add FOREIGN KEY constraint to Books table

Modify the Books table creation statement to add a FOREIGN KEY constraint on AuthorID that references AuthorID in the Authors table.

SQL

CREATE TABLE Authors (
    AuthorID INT PRIMARY KEY,
    Name TEXT
);

-- Modify the Books table to add FOREIGN KEY constraint on AuthorID
CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title TEXT,
    AuthorID INT,
    -- Your code here
);

Need a hint?

Use FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID) inside the Books table definition.

4

Complete the database setup with both tables

Ensure the full SQL code includes the creation of both Authors and Books tables, with the FOREIGN KEY constraint on Books.AuthorID referencing Authors.AuthorID.

SQL

-- Confirm the full SQL code for both tables with FOREIGN KEY constraint
-- Your code here

Need a hint?

Make sure both tables are created and the foreign key constraint is present in the Books table.