SQL UNIQUE Constraint

Definition

A UNIQUE constraint in SQL is used to ensure that the values in a specific column or set of columns are distinct, i.e., no duplicates are allowed. When you create a UNIQUE constraint, the database engine automatically creates a unique index on the column(s) specified in the constraint.

You can add a UNIQUE constraint to a column or set of columns in a table by using the ALTER TABLE statement, like so:

ALTER TABLE table_name 
ADD CONSTRAINT constraint_name 
UNIQUE (column1, column2);

Alternatively, you can add a UNIQUE constraint while creating a new table, using the CREATE TABLE statement:

CREATE TABLE table_name (
    column1 data_type UNIQUE, 
    column2 data_type
    );

You can also specify more than one UNIQUE constraint for a table or you can combine multiple column to create unique constraint on the combination of columns.

CREATE TABLE table_name (
    column1 data_type, 
    column2 data_type, 
    column3 data_type, 
    UNIQUE (column1, column2), 
    UNIQUE (column2, column3)
    );

You can use the following command to drop a previously created UNIQUE constraint by the name of the constraint.

ALTER TABLE table_name 
DROP CONSTRAINT constraint_name;

Examples

A UNIQUE constraint in SQL is used to ensure that the data in a specific column or set of columns is unique across all rows in a table. Here's an example of how to create a UNIQUE constraint on the email column in a table called users:

CREATE TABLE users (
    id INT PRIMARY KEY, 
    email VARCHAR(255) NOT NULL, 
    UNIQUE (email)
    );

In this example, the UNIQUE constraint is applied to the email column, so no two rows in the users table can have the same email address.

You can also specify multiple columns for a UNIQUE constraint, like this:

CREATE TABLE orders (
    order_id INT PRIMARY KEY, 
    item_id INT NOT NULL, 
    customer_id INT NOT NULL, 
    UNIQUE (item_id, customer_id)
);

Here, the UNIQUE constraint is applied to the combination of values in the item_id and customer_id columns, so no two rows in the orders table can have the same combination of values in those two columns.

You can also add a unique constraint to an existing table with ALTER TABLE statement

ALTER TABLE users 
ADD UNIQUE (email);

This statement will add unique constraint on the email column in users table.

Best Practices

There are a few best practices to keep in mind when working with UNIQUE constraints in SQL:

• Use UNIQUE constraints instead of PRIMARY KEY constraints when you have multiple unique columns in a table, or when you want to enforce unique values across multiple columns.

• Understand the difference between NULL and non-NULL values. A UNIQUE constraint allows multiple NULL values, while a PRIMARY KEY constraint does not.

• Define UNIQUE constraints on columns that are frequently used in joins, as it helps to ensure the integrity of the data and also improves the performance of your queries.

• When you are adding a UNIQUE constraint to an existing table, make sure to check for any duplicate values that may already exist in the table. If there are duplicates, you will need to either remove them or update them before adding the constraint.

• Be aware of the indexing that happens in the background, when you define a UNIQUE constraint on a column, a UNIQUE index is created automatically and while this could increase performance, it can have an impact on disk space.