Unique index behavior in SQL - Mini Project: Build & Apply

Understanding Unique Index Behavior in SQL

📖 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 simple SQL table with a unique index on the product code column to prevent duplicate product codes.

📋 What You'll Learn

Create a table named products with columns id (integer primary key), product_code (text), and name (text).

Add a unique index on the product_code column.

Insert sample data with unique product codes.

Attempt to insert a duplicate product code to understand unique index behavior.

💡 Why This Matters

🌍 Real World

Unique indexes are used in databases to ensure data integrity by preventing duplicate entries in important columns like product codes, user emails, or IDs.

💼 Career

Database administrators and developers use unique indexes to enforce business rules and maintain clean, reliable 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, product_code as text, and name as text.

SQL

-- Your code here to create the products table

Need a hint?

Use CREATE TABLE with the specified columns and types. Remember to set id as the primary key.

2

Add a unique index on product_code

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

SQL

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    product_code TEXT,
    name TEXT
);
-- Your code here to create a unique index on product_code

Need a hint?

Use CREATE UNIQUE INDEX with the index name and specify the column product_code.

3

Insert sample unique data

Write SQL INSERT statements to add these two products into the products table: (1, 'P1001', 'Red Shirt') and (2, 'P1002', 'Blue Jeans').

SQL

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    product_code TEXT,
    name TEXT
);

CREATE UNIQUE INDEX idx_unique_product_code ON products(product_code);
-- Your code here to insert two unique products

Need a hint?

Use two separate INSERT INTO statements with the exact values given.

4

Try inserting a duplicate product_code

Write a SQL INSERT statement to add a product with id 3, product_code 'P1001', and name 'Green Hat'. This will test the unique index behavior.

SQL

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    product_code TEXT,
    name TEXT
);

CREATE UNIQUE INDEX idx_unique_product_code ON products(product_code);

INSERT INTO products (id, product_code, name) VALUES (1, 'P1001', 'Red Shirt');
INSERT INTO products (id, product_code, name) VALUES (2, 'P1002', 'Blue Jeans');
-- Your code here to insert a product with duplicate product_code 'P1001'

Need a hint?

Try inserting a product with the same product_code 'P1001' to see how the unique index prevents duplicates.