MySQL UNIQUE Key

Introduction

In this chapter, we will learn about the UNIQUE key in MySQL. A UNIQUE key ensures that all values in a column or a set of columns are distinct from each other. Unlike the primary key, a table can have multiple UNIQUE keys. We will cover the syntax for defining UNIQUE keys, examples, and important considerations for using UNIQUE keys in MySQL.

Syntax

The basic syntax for defining a UNIQUE key in MySQL is:

When creating a table:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
    UNIQUE (unique_column)
);

For a composite unique key:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
    UNIQUE (unique_column1, unique_column2)
);

Adding a UNIQUE key to an existing table:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (unique_column);

Example

Let’s go through an example where we create a table with a UNIQUE key and then add a UNIQUE key to another table.

Full Example

1. Create a Database:

CREATE DATABASE company;

2. Select the Database:

USE company;

3. Create the Employees Table with a UNIQUE Key:

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    department_id INT
);

4. Insert Data into the Employees Table:

INSERT INTO employees (first_name, last_name, email, department_id) VALUES
('Rahul', 'Sharma', 'rahul.sharma@example.com', 1),
('Priya', 'Singh', 'priya.singh@example.com', 2),
('Amit', 'Kumar', 'amit.kumar@example.com', 1),
('Neha', 'Verma', 'neha.verma@example.com', 3),
('Sahil', 'Mehta', 'sahil.mehta@example.com', 2);

5. Query the Employees Table:

Let’s perform a simple query to verify our data.

SELECT * FROM employees;

Output

In this example, the email column is a UNIQUE key, ensuring that no two employees can have the same email address.

6. Create the Projects Table with a Composite Unique Key:

CREATE TABLE projects (
    project_id INT PRIMARY KEY AUTO_INCREMENT,
    project_name VARCHAR(100) NOT NULL,
    department_id INT,
    UNIQUE (project_name, department_id)
);

7. Insert Data into the Projects Table:

INSERT INTO projects (project_name, department_id) VALUES
('Project A', 1),
('Project B', 2),
('Project C', 1),
('Project D', 3);

8. Query the Projects Table:

Let’s perform a simple query to verify our data.

SELECT * FROM projects;

Output

In this example, the combination of project_name and department_id must be unique, ensuring no two projects in the same department have the same name.

Adding a UNIQUE Key to an Existing Table

Let’s assume we have a table tasks and we want to add a UNIQUE key to it.

1. Create the Tasks Table:

CREATE TABLE tasks (
    task_id INT PRIMARY KEY AUTO_INCREMENT,
    task_name VARCHAR(100),
    project_id INT,
    employee_id INT
);

2. Add a UNIQUE Key to the Tasks Table:

ALTER TABLE tasks
ADD CONSTRAINT unique_task UNIQUE (task_name, project_id);

3. Insert Data into the Tasks Table:

INSERT INTO tasks (task_name, project_id, employee_id) VALUES
('Task 1', 1, 1),
('Task 2', 1, 2),
('Task 3', 2, 1),
('Task 4', 3, 3);

4. Query the Tasks Table:

Let’s perform a simple query to verify our data.

SELECT * FROM tasks;

Output

In this example, the combination of task_name and project_id must be unique, ensuring no two tasks in the same project have the same name.

Important Considerations

• Uniqueness: The UNIQUE key ensures that all values in the specified column or combination of columns are unique across all rows in the table.
• Multiple UNIQUE Keys: Unlike the primary key, a table can have multiple UNIQUE keys.
• NULL Values: Columns with a UNIQUE constraint can contain NULL values, but each NULL is considered unique.
• Indexing: UNIQUE keys are automatically indexed in MySQL, improving the performance of queries that use the unique key for lookups.

Conclusion

The UNIQUE key is an essential feature in MySQL for ensuring that all values in a column or a set of columns are distinct from each other. This chapter covered the syntax for defining UNIQUE keys, provided a full example with single and composite unique keys, and discussed important considerations.