SQL Keys - Unique Key

The UNIQUE constraint ensures that all values in a column are distinct, with the exception of NULL values. A table can have multiple UNIQUE constraints.

Syntax

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

Example

CREATE TABLE employees (
   id INT unique,
   name VARCHAR(50),
   age INT
);

Output

If we try to insert a duplicate value in the id column, we will encounter an error message.

Explanation

The UNIQUE constraint is used to ensure that the values in a column (or a group of columns) are unique. Unlike the primary key, the unique key does not automatically generate a clustered index. We can create a unique index for a unique key to improve search performance.

Use

The UNIQUE constraint is useful for columns that should not have duplicated values, but where a primary key is not appropriate. For example, we may want to enforce uniqueness for a customer's email address, because there should not be multiple accounts with the same email address.

Important Points

• A table can have multiple UNIQUE constraints.
• The UNIQUE constraint does not allow duplicate values in a column, but it allows NULL values.
• A UNIQUE index can be created for a unique key to improve search performance.

Summary

In summary, the UNIQUE constraint can be used to ensure that values in a column (or a group of columns) are unique. It is useful when a primary key is not appropriate, but we still want to enforce uniqueness for a particular column.