Composite primary keys in SQL - Mini Project: Build & Apply

Creating a Table with Composite Primary Keys

📖 Scenario: You are working on a small library database. Each book can have multiple authors, and each author can write multiple books. To keep track of which author wrote which book, you need a table that connects authors and books.

🎯 Goal: Create a table called BookAuthors that uses a composite primary key made up of BookID and AuthorID. This will ensure that each combination of book and author is unique.

📋 What You'll Learn

Create a table named BookAuthors

Include two columns: BookID and AuthorID, both integers

Set a composite primary key using both BookID and AuthorID

💡 Why This Matters

🌍 Real World

Composite primary keys are used in many-to-many relationships, like linking books and authors, students and courses, or products and orders.

💼 Career

Understanding composite primary keys is important for designing relational databases that accurately model complex relationships and maintain data integrity.

1

Create the BookAuthors table with columns

Write a SQL statement to create a table called BookAuthors with two columns: BookID and AuthorID, both of type INT.

SQL

CREATE TABLE BookAuthors (
    BookID INT,
    AuthorID INT
);
-- Your code here

Need a hint?

Use CREATE TABLE followed by the table name and define the columns with their data types inside parentheses.

2

Add a composite primary key constraint

Modify the BookAuthors table creation statement to add a composite primary key constraint on both BookID and AuthorID.

SQL

CREATE TABLE BookAuthors (
    BookID INT,
    AuthorID INT,
    -- Add composite primary key constraint here
);

Need a hint?

Use PRIMARY KEY (BookID, AuthorID) inside the table definition to set the composite key.

3

Insert sample data into BookAuthors

Write SQL insert statements to add these exact rows into BookAuthors: (1, 101), (1, 102), and (2, 101).

SQL

-- Insert the rows (1, 101), (1, 102), and (2, 101) into BookAuthors
-- Your code here

Need a hint?

Use INSERT INTO BookAuthors (BookID, AuthorID) VALUES (..., ...); for each row.

4

Try inserting a duplicate row to see the primary key in action

Write a SQL insert statement to add the row (1, 101) again into BookAuthors. This should fail because of the composite primary key.

SQL

-- Try to insert the duplicate row (1, 101) into BookAuthors
-- Your code here

Need a hint?

Try inserting the same row again to see how the database prevents duplicates with the composite primary key.