Primary keys and uniqueness in SQL - Mini Project: Build & Apply

Primary Keys and Uniqueness in SQL

📖 Scenario: You are creating a simple database table to store information about books in a library. Each book must have a unique identifier so that it can be easily found and managed.

🎯 Goal: Build a table called Books with a primary key and a unique constraint to ensure data integrity.

📋 What You'll Learn

Create a table named Books with columns BookID, Title, and ISBN.

Set BookID as the primary key.

Add a unique constraint on the ISBN column to prevent duplicate ISBN numbers.

💡 Why This Matters

🌍 Real World

Libraries, bookstores, and inventory systems use primary keys and unique constraints to keep track of items without confusion.

💼 Career

Database administrators and developers must design tables with keys and constraints to ensure data accuracy and efficient data retrieval.

1

Create the Books table with columns

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

SQL

CREATE TABLE Books (
    BookID INT,
    Title TEXT,
    ISBN TEXT
);
-- Your code here

Need a hint?

Use CREATE TABLE followed by the table name and define each column with its data type inside parentheses.

2

Add a primary key to BookID

Modify the Books table creation statement to set BookID as the primary key.

SQL

CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title TEXT,
    ISBN TEXT
);
-- Your code here

Need a hint?

Add PRIMARY KEY right after the BookID INT declaration.

3

Add a unique constraint on ISBN

Update the Books table creation statement to add a unique constraint on the ISBN column to prevent duplicate ISBN values.

SQL

CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title TEXT,
    ISBN TEXT UNIQUE
);
-- Your code here

Need a hint?

Add UNIQUE right after the ISBN TEXT declaration.

4

Complete the Books table with primary key and unique constraint

Ensure the final CREATE TABLE Books statement includes BookID as the primary key and ISBN as unique.

SQL

CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title TEXT,
    ISBN TEXT UNIQUE
);
-- Your code here

Need a hint?

Double-check that both PRIMARY KEY and UNIQUE constraints are present in the table definition.