• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL Unique

← Previous Next →

The MySQL UNIQUE constraint ensures that all values in a column or a combination of columns are unique. This means no two rows in a table can have the same value in that column or set of columns.

Using UNIQUE is essential for preventing duplicate data, improving data integrity, and enforcing rules for identifiers such as emails, usernames, or product codes.

What Is UNIQUE Constraint?

In MySQL, the UNIQUE constraint is applied to a column or combination of columns to guarantee uniqueness across all rows.

Key Points:

  • Prevents duplicate values in the specified column(s).

  • Can be applied at column level or table level.

  • Multiple UNIQUE constraints can exist in a table.

  • Often combined with NOT NULL or PRIMARY KEY constraints.

Syntax for UNIQUE

Column-Level:

CREATE TABLE table_name (
    column_name datatype UNIQUE,
    ...
);

Table-Level (for multiple columns):

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    CONSTRAINT constraint_name UNIQUE (column1, column2)
);

Explanation:

  • column_name: Column to enforce uniqueness.

  • constraint_name: Optional name for the UNIQUE constraint.

  • Multiple columns can enforce uniqueness on combinations (composite unique key).

Creating a Table with UNIQUE Columns

Example: Create a students table with unique emails:

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    student_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE
);

Explanation:

  • email column cannot have duplicate values.

  • Multiple students cannot share the same email.

  • student_id is the primary key and already unique.

UNIQUE on Multiple Columns (Composite Unique Key)

Sometimes, uniqueness is required across a combination of columns:

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    CONSTRAINT uc_student_course UNIQUE (student_id, course_id)
);

Explanation:

  • Ensures a student cannot enroll in the same course more than once.

  • Each combination of student_id and course_id must be unique.

Adding a UNIQUE Constraint to an Existing Table

If a table already exists, you can add a UNIQUE constraint using ALTER TABLE:

ALTER TABLE students
ADD CONSTRAINT uc_email UNIQUE (email);

Explanation:

  • Adds a unique constraint to the email column.

  • Prevents duplicate emails for new inserts or updates.

UNIQUE with NOT NULL

Although UNIQUE allows multiple NULL values in a column, combining it with NOT NULL ensures every value is valid and unique:

CREATE TABLE courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    course_code VARCHAR(10) UNIQUE NOT NULL,
    course_name VARCHAR(50) NOT NULL
);

Explanation:

  • course_code must be unique and cannot be NULL.

  • Guarantees every course has a valid, distinct code.

Handling UNIQUE Violations

If you try to insert a duplicate value into a UNIQUE column:

INSERT INTO students (student_name, email)
VALUES ('Meera', 'meera@example.com');

INSERT INTO students (student_name, email)
VALUES ('Riya', 'meera@example.com');

Result: MySQL throws an error: Duplicate entry 'meera@example.com' for key 'email'.

Explanation:

  • The second insert fails because email must be unique.

Practical Use Cases

  1. User Accounts: Ensure usernames or emails are unique.

  2. Product Codes: Prevent duplicate SKU or product IDs.

  3. Enrollment Systems: Prevent multiple enrollments in the same course for the same student.

  4. Inventory Management: Unique identifiers for items or batches.

  5. Financial Systems: Unique invoice numbers or transaction IDs.

Best Practices

  1. Apply UNIQUE Early: Enforce uniqueness when creating the table.

  2. Use Composite UNIQUE Keys: Ensure uniqueness across multiple columns when needed.

  3. Combine with NOT NULL: Prevent NULL values from bypassing uniqueness constraints.

  4. Name Constraints Clearly: Use descriptive names for easier database maintenance.

  5. Validate Data Before Insert: Avoid errors by checking for duplicates at the application level.

Common Mistakes to Avoid

  1. Assuming PRIMARY KEY Is the Only Unique Column: UNIQUE can be applied to other columns.

  2. Ignoring NULL Values: UNIQUE allows multiple NULLs unless combined with NOT NULL.

  3. Duplicate Data Before Adding Constraint: Adding UNIQUE on a table with duplicates will fail.

  4. Overusing UNIQUE Constraints: Too many can complicate inserts and updates.

  5. Not Testing Inserts and Updates: Always test constraints with real data scenarios.

Summary of the Tutorial

The MySQL UNIQUE constraint is a powerful tool to prevent duplicate values and enforce data integrity.

  • Can be applied to single columns or multiple columns.

  • Works with or without NOT NULL.

  • Essential for identifiers, codes, and critical reference data.

  • Helps maintain clean, reliable, and consistent databases.

Using UNIQUE correctly ensures your database avoids redundant data, enforces business rules, and supports accurate reporting and analytics.

Practice Questions

  1. Create a table students with student_id as PRIMARY KEY and email as UNIQUE.

  2. Add a UNIQUE constraint to the course_code column in the existing courses table.

  3. Create a table enrollments with a composite UNIQUE constraint on student_id and course_id.

  4. Insert two students with the same email and observe the UNIQUE constraint behavior.

  5. Create a table products with product_code as UNIQUE and product_name as NOT NULL.

  6. Add a UNIQUE constraint to a column username in the users table that already contains some duplicate values and handle errors.

  7. Create a table departments with department_id as PRIMARY KEY and department_name as UNIQUE.

  8. Combine UNIQUE and NOT NULL on the order_number column in an orders table.

  9. Modify an existing table employees to add a UNIQUE constraint on the email column.

  10. Insert multiple rows into a table with a composite UNIQUE constraint and test for duplicates.

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

MySQL

online coding class codepractice

Introduction to MySQL

MySQL SQL Basics

MySQL Functions & Operators

MySQL Joins & Unions

MySQL Grouping & Filtering

Database & Table Management

MySQL Constraints

Date & Views

References

codepractice logo
Login
Go Back Top