SQL UNIQUE Key

Introduction

In this chapter, we will learn about the UNIQUE key constraint in SQL. The UNIQUE key constraint ensures that all values in a column are different. This chapter will cover the syntax, usage, and provide examples to help you understand how to use the UNIQUE key constraint effectively.

What is a UNIQUE Key?

A UNIQUE key is a constraint that ensures all values in a column or a set of columns are unique. Unlike the primary key, a table can have multiple UNIQUE constraints. The UNIQUE key allows NULL values, but only one NULL value per column.

Syntax for UNIQUE Key

Basic Syntax

When creating a table, you can define a column as unique using the following syntax:

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

You can also define a unique constraint on multiple columns (composite unique key):

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

Adding UNIQUE Constraint to an Existing Table

To add a UNIQUE constraint to an existing table:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column_name);

Step-by-Step Example

1. Create a Sample Table with UNIQUE Key

First, we will create a sample table named employees with a UNIQUE key.

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100) UNIQUE,
    department_id INT,
    salary DECIMAL(10, 2)
);

2. Insert Sample Data

INSERT INTO employees (first_name, last_name, email, department_id, salary)
VALUES
('Ramesh', 'Kumar', 'ramesh.kumar@example.com', 1, 50000),
('Sita', 'Patel', 'sita.patel@example.com', 2, 60000),
('Arjun', 'Singh', 'arjun.singh@example.com', 1, 55000),
('Priya', 'Sharma', 'priya.sharma@example.com', 2, 62000);

3. Verify the UNIQUE Key Constraint

To verify that the email column is unique, try to insert a duplicate value:

INSERT INTO employees (first_name, last_name, email, department_id, salary)
VALUES ('Ravi', 'Verma', 'ramesh.kumar@example.com', 3, 70000);

This command will result in an error because the email value 'ramesh.kumar@example.com' already exists in the table, demonstrating that the UNIQUE key constraint enforces unique values.

4. Create a Table with Composite UNIQUE Key

Now, we will create another table named projects with a composite UNIQUE key.

CREATE TABLE projects (
    project_id INT,
    project_name VARCHAR(100),
    start_date DATE,
    end_date DATE,
    UNIQUE (project_name, start_date)
);

5. Insert Sample Data into the projects Table

INSERT INTO projects (project_id, project_name, start_date, end_date)
VALUES
(1, 'Project A', '2023-01-01', '2023-06-30'),
(2, 'Project B', '2023-02-01', '2023-07-31'),
(3, 'Project A', '2023-03-01', '2023-08-31');

6. Verify the Composite UNIQUE Key Constraint

To verify that the combination of project_name and start_date is unique, try to insert a duplicate value:

INSERT INTO projects (project_id, project_name, start_date, end_date)
VALUES (4, 'Project A', '2023-01-01', '2023-12-31');

This command will result in an error because the combination of project_name 'Project A' and start_date '2023-01-01' already exists in the table, demonstrating that the composite UNIQUE key constraint enforces unique combinations.

Adding UNIQUE Constraint to an Existing Table

If you have an existing table and want to add a UNIQUE constraint, you can use the ALTER TABLE statement.

Example

Assume we have an existing table named employees without a UNIQUE constraint on the email column:

ALTER TABLE employees
ADD CONSTRAINT unique_email UNIQUE (email);

Verify the Addition of UNIQUE Constraint

To verify the addition of the UNIQUE constraint, try to insert a duplicate email:

INSERT INTO employees (first_name, last_name, email, department_id, salary)
VALUES ('Ravi', 'Verma', 'ramesh.kumar@example.com', 3, 70000);

This command will result in an error because the email value 'ramesh.kumar@example.com' already exists in the table, demonstrating that the UNIQUE key constraint enforces unique values.

Conclusion

The UNIQUE key constraint is used for ensuring that all values in a column or a set of columns are unique. This chapter covered the basic syntax for defining UNIQUE keys, inserting data while maintaining unique values, and using composite UNIQUE keys. Understanding how to define and use UNIQUE keys effectively will enhance your ability to design robust and reliable database schemas.