MySQL UNIQUE

Preventing duplicate values in your database

🎯 What is UNIQUE?

The UNIQUE constraint ensures all values in a column are different from each other. It prevents duplicate entries, maintaining data uniqueness across records in your table. 


-- Creating a table with UNIQUE constraint
CREATE TABLE users (
    id INT,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100) UNIQUE
);

Output:

Table 'users' created with UNIQUE constraints on username and email.

Key UNIQUE Concepts

🔐

No Duplicates

Ensures unique values only 

email VARCHAR(100) UNIQUE
📧

Multiple Columns

Apply to several columns 

UNIQUE(email, phone)
✨

NULL Allowed

Can have multiple NULLs 

phone VARCHAR(15) UNIQUE
🔍

Auto Index

Creates index automatically 

username VARCHAR(50) UNIQUE

🔹 Creating UNIQUE Constraint

Define UNIQUE constraints when creating tables to prevent duplicate values. You can apply it to single columns or combine multiple columns for composite uniqueness, ensuring data integrity from the start of your database design. 

CREATE TABLE customers (
    customer_id INT,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(15) UNIQUE,
    tax_id VARCHAR(20) UNIQUE
);

Output:

✓ Table created with UNIQUE constraints on email, phone, and tax_id

🔹 UNIQUE with Constraint Name

Naming your UNIQUE constraints makes them easier to identify and manage later. Use the CONSTRAINT keyword followed by a descriptive name, which helps when you need to drop or modify the constraint in the future. 

CREATE TABLE products (
    product_id INT,
    product_code VARCHAR(20),
    barcode VARCHAR(50),
    CONSTRAINT uc_product_code UNIQUE (product_code),
    CONSTRAINT uc_barcode UNIQUE (barcode)
);

Output:

✓ Table created with named UNIQUE constraints

🔹 Composite UNIQUE Constraint

A composite UNIQUE constraint ensures the combination of multiple column values is unique, even if individual values repeat. This is useful for scenarios like preventing duplicate course enrollments where student and course together must be unique. 

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    semester VARCHAR(20),
    CONSTRAINT uc_enrollment UNIQUE (student_id, course_id, semester)
);

Output:

✓ Composite UNIQUE constraint created on (student_id, course_id, semester)

🔹 Adding UNIQUE to Existing Table

You can add UNIQUE constraints to existing tables using ALTER TABLE. Before adding the constraint, ensure there are no duplicate values in the column, or MySQL will reject the operation with a duplicate entry error. 

-- Add UNIQUE constraint to existing column
ALTER TABLE customers 
ADD CONSTRAINT uc_email UNIQUE (email);

-- Add UNIQUE to multiple columns
ALTER TABLE customers 
ADD CONSTRAINT uc_phone_email UNIQUE (phone, email);

Output:

✓ UNIQUE constraints added successfully

🔹 Testing UNIQUE Constraint

When you try to insert duplicate values into a UNIQUE column, MySQL prevents the operation and returns an error. This automatic validation protects your data integrity without requiring additional application-level checks for duplicates. 

-- First insert succeeds
INSERT INTO customers (customer_id, email) 
VALUES (1, '[email protected]');

-- Second insert with same email fails
INSERT INTO customers (customer_id, email) 
VALUES (2, '[email protected]');
-- Error: Duplicate entry '[email protected]' for key 'email'

Output:

✓ First insert successful

✗ Second insert failed: Duplicate entry

🔹 Dropping UNIQUE Constraint

Remove UNIQUE constraints when you no longer need to enforce uniqueness on a column. Use the constraint name if you defined one, or use the column name as the index name to drop the constraint. 

-- Drop UNIQUE constraint by name
ALTER TABLE customers 
DROP INDEX uc_email;

-- Drop UNIQUE constraint by column name
ALTER TABLE customers 
DROP INDEX phone;

Output:

✓ UNIQUE constraints dropped successfully

🧠 Test Your Knowledge

What does the UNIQUE constraint prevent?