UNIQUE constraints in MySQL - Mini Project: Build & Apply

Using UNIQUE Constraints in MySQL

📖 Scenario: You are creating a simple database for a small online bookstore. You want to make sure that each book's ISBN number is unique so that no two books can have the same ISBN.

🎯 Goal: Build a MySQL table called books with columns for id, title, and isbn. Add a UNIQUE constraint on the isbn column to prevent duplicate ISBN numbers.

📋 What You'll Learn

Create a table named books with columns id (integer, primary key, auto-increment), title (varchar 100), and isbn (varchar 20).

Add a UNIQUE constraint on the isbn column.

Use standard MySQL syntax.

💡 Why This Matters

🌍 Real World

Ensuring unique values like ISBNs in a bookstore database prevents data errors and duplicates.

💼 Career

Database developers and administrators often use UNIQUE constraints to maintain data integrity in real applications.

1

Create the books table with columns

Write a MySQL statement to create a table called books with three columns: id as an integer primary key with auto-increment, title as a varchar(100), and isbn as a varchar(20).

MySQL

CREATE TABLE books (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(100),
    isbn VARCHAR(20)
);
-- Your code here

Need a hint?

Use CREATE TABLE with the column definitions separated by commas. Remember to set id as the primary key and auto-increment.

2

Add a UNIQUE constraint on the isbn column

Modify the books table creation statement to add a UNIQUE constraint on the isbn column to ensure no duplicate ISBNs.

MySQL

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100),
    isbn VARCHAR(20) UNIQUE
);
-- Your code here

Need a hint?

Add the keyword UNIQUE right after the isbn VARCHAR(20) column definition.

3

Insert sample data into the books table

Write two INSERT INTO books statements to add these books:
1. Title: 'Learn SQL', ISBN: '123-4567890123'
2. Title: 'Mastering Databases', ISBN: '987-6543210987'

MySQL

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100),
    isbn VARCHAR(20) UNIQUE
);

INSERT INTO books (title, isbn) VALUES ('Learn SQL', '123-4567890123');
-- Your code here

Need a hint?

Use two separate INSERT INTO books (title, isbn) VALUES (...) statements with the exact titles and ISBNs given.

4

Try inserting a duplicate ISBN to see the UNIQUE constraint in action

Write an INSERT INTO books statement to add a book with title 'Duplicate ISBN Test' and ISBN '123-4567890123' (which already exists). This should fail due to the UNIQUE constraint.

MySQL

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100),
    isbn VARCHAR(20) UNIQUE
);

INSERT INTO books (title, isbn) VALUES ('Learn SQL', '123-4567890123');
INSERT INTO books (title, isbn) VALUES ('Mastering Databases', '987-6543210987');

INSERT INTO books (title, isbn) VALUES ('Duplicate ISBN Test', '123-4567890123');
-- Your code here

Need a hint?

Try inserting a book with the same ISBN as the first book to see the UNIQUE constraint prevent duplicates.