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

Creating a Table with PRIMARY KEY Constraint

📖 Scenario: You are setting up 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: Create a table named Books with columns for BookID, Title, and Author. Ensure that BookID is the PRIMARY KEY to uniquely identify each book.

📋 What You'll Learn

Create a table called Books

Add a column BookID of type INT

Add columns Title and Author of type VARCHAR(100)

Set BookID as the PRIMARY KEY of the table

💡 Why This Matters

🌍 Real World

Databases use PRIMARY KEY constraints to uniquely identify each record, like a library catalog number for books.

💼 Career

Understanding PRIMARY KEY constraints is essential for database design and data integrity in software development and data management jobs.

1

Create the Books table with columns

Write a CREATE TABLE statement to create a table named Books with columns BookID as INT, Title as VARCHAR(100), and Author as VARCHAR(100).

SQL

CREATE TABLE Books (
    BookID INT,
    Title VARCHAR(100),
    Author VARCHAR(100)
);
-- Your code here

Need a hint?

Use CREATE TABLE with column names and data types separated by commas.

2

Add PRIMARY KEY constraint to BookID

Modify the CREATE TABLE statement to add a PRIMARY KEY constraint on the BookID column.

SQL

CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title VARCHAR(100),
    Author VARCHAR(100)
);
-- Your code here

Need a hint?

Add PRIMARY KEY right after the data type of BookID.

3

Insert sample data into Books table

Write an INSERT INTO statement to add a book with BookID 1, Title 'The Great Gatsby', and Author 'F. Scott Fitzgerald' into the Books table.

SQL

INSERT INTO Books (BookID, Title, Author) VALUES (1, 'The Great Gatsby', 'F. Scott Fitzgerald');
-- Your code here

Need a hint?

Use INSERT INTO with column names and VALUES with matching data.

4

Try inserting a duplicate BookID to see PRIMARY KEY in action

Write an INSERT INTO statement to add another book with the same BookID 1, Title '1984', and Author 'George Orwell'. This should fail because BookID is a PRIMARY KEY.

SQL

INSERT INTO Books (BookID, Title, Author) VALUES (1, '1984', 'George Orwell');
-- Your code here

Need a hint?

Try inserting a row with the same BookID to see the PRIMARY KEY constraint prevent duplicates.