Unique indexes in MySQL - Mini Project: Build & Apply

Creating and Using Unique Indexes in MySQL

📖 Scenario: You are managing a small online store database. You want to ensure that each product has a unique product code so that no two products can share the same code.

🎯 Goal: Build a MySQL table for products and add a unique index on the product code to prevent duplicate entries.

📋 What You'll Learn

Create a table named products with columns id, name, and product_code

Add a unique index on the product_code column

Insert sample data with unique product codes

Attempt to insert a duplicate product code to see the unique index in action

💡 Why This Matters

🌍 Real World

Unique indexes are used in databases to prevent duplicate entries for important fields like product codes, email addresses, or user IDs.

💼 Career

Database administrators and backend developers use unique indexes to maintain data integrity and avoid errors caused by duplicate data.

1

Create the products table

Write a SQL statement to create a table called products with three columns: id as an integer primary key, name as a VARCHAR(100), and product_code as a VARCHAR(20).

MySQL

-- Your code here to create the products table

Need a hint?

Use CREATE TABLE with the specified columns and types.

2

Add a unique index on product_code

Write a SQL statement to add a unique index named unique_product_code on the product_code column of the products table.

MySQL

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  product_code VARCHAR(20)
);
-- Your code here to add a unique index on product_code

Need a hint?

Use CREATE UNIQUE INDEX followed by the index name and the table and column.

3

Insert sample products with unique codes

Write SQL statements to insert these products into the products table: (1, 'Laptop', 'LP1001') and (2, 'Smartphone', 'SP2002').

MySQL

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  product_code VARCHAR(20)
);

CREATE UNIQUE INDEX unique_product_code ON products(product_code);
-- Your code here to insert two products with unique product codes

Need a hint?

Use INSERT INTO with the specified values for each product.

4

Try inserting a duplicate product_code

Write a SQL statement to insert a new product with id 3, name 'Tablet', and product_code 'LP1001' which duplicates an existing product_code to test the unique index.

MySQL

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  product_code VARCHAR(20)
);

CREATE UNIQUE INDEX unique_product_code ON products(product_code);

INSERT INTO products (id, name, product_code) VALUES (1, 'Laptop', 'LP1001');
INSERT INTO products (id, name, product_code) VALUES (2, 'Smartphone', 'SP2002');
-- Your code here to insert a product with a duplicate product_code 'LP1001'

Need a hint?

Try inserting a product with a product_code that already exists to see the unique index prevent duplicates.