UNIQUE Constraint - Scaler Topics

Overview

UNIQUE constraint is a column-level constraint used to ensure that the constrained column should only contain a unique value (or value should be different) in the specified column. It can contain a null value because a null value denotes the absence of a value; therefore, it can't be equal to any other null value in the table. UNIQUE constraint is usually used to identify each tuple in the table uniquely.

What is a Unique Constraint in SQL?

Let us first understand what are constraints in SQL. Suppose we are creating a table to store the data of people. We need a primary key that must be unique and should not contain any null value. This will help to identify records uniquely. It will also help in faster retrieval of data from the queries that we make. So, we have to use some constraints like the PRIMARY KEY constraint and UNIQUE KEY constraint.

In simple terms, constraints mean some kind of restrictions. Formally, we can say that constraints are the set of rules imposed on a table's column(s) to restrict the data that can be inserted into a given column.

UNIQUE constraint is a column-level constraint used to ensure that the constrained column only contains a unique value(or value should be different) in the specified column. It can contain a null value. UNIQUE Constraint is usually used to uniquely identify each tuple in the table.

A UNIQUE constraint should be enforced on the column that is supposed to contain some ID kind of thing that cannot be the same for more than one.

For example – The roll number column in a student table must be unique.

Note: One important thing to note with the UNIQUE constraint is that - Primary Keys are always unique (by default), but the UNIQUE constrained column may or may not be a Primary Key.

SYNTAX: The following are the two ways to impose constraints in SQL. Let’s see their syntax:

1. CREATE A TABLE

2. ALTER TABLE command:

UNIQUE Constraint VS. PRIMARY KEY Constraint

PRIMARY KEY constraint ensures that the constrained column should have unique and has NOT NULL values. The UNIQUE and NOT NULL are, by default, applied to the primary key. It is usually used to index the table or uniquely identify each tuple in the table.

The PRIMARY KEY constraint should be enforced on the column that is supposed to be the table’s primary key. Primary keys help to retrieve query results from a table.

For example – The roll number column in a student table can be made the primary key as every student has a roll number(NOT NULL), and no two students can have the same roll number(UNIQUE constraint).

The UNIQUE constraint allows NULL values, but the PRIMARY KEY constraint does not allow null values.

We can say that: PRIMARY KEY CONSTRAINT = UNIQUE CONSTRAINT + NOT NULL CONSTRAINT

SQL UNIQUE Constraint on CREATE TABLE

As we know, we can impose constraint(s) on columns at the time of the creation of tables and after the creation of tables (altering table). Let us first know about imposing a constraint on a column at the time of table creation.

Syntax:

Let us take an example to understand the syntax better. For example, Let's create a Person table with three columns, namely - ID, name, and age. The "ID" column is a UNIQUE column. This means that the ID column will only contain distinct values.

Let's see the syntax:

Note: NOT NULL is also a SQL constraint. NOT NULL constraint ensures that the constrained column does not contain any NULL value in any row or tuple.

SQL UNIQUE Constraint on ALTER TABLE

In the earlier section, we have seen the example and syntax of unique constraints using the CREATE TABLE command. Let us now know about imposing constraints on a column after the creation of the table.

Syntax:

Let us take the same Person table example to understand the syntax better. Suppose we need to impose a unique constraint on the "ID" column. This means that the ID column will only contain distinct values.

Let's see the syntax

DROP a UNIQUE Constraint

Now, as we know how we can impose constraints on column(s) of a table, let us learn how we can remove or drop constraints from column(s). We can use the DROP INDEX SQL command for the same.

Let us take the same Person table example to understand the syntax better. The "ID" column is a unique constraint column. We can remove this constraint from the "ID" column. This means that the ID column can now contain similar values.

Let's see the syntax:

Note: We should carefully remove the UNIQUE constraint. If we remove a unique constraint from an attribute that is part of the primary key, the database may become inconsistent.

Add UNIQUE Constraints to Existing Columns

We can also add a UNIQUE constraint on column(s) of an existing column. We can use the ADD CONSTRAINT SQL command and the ALTER TABLE command.

Let us see the syntax:

Let us take the example to understand the working and syntax better. Suppose we have a student’s table with three columns: roll number, name, and age. We want to convert the roll_number column into a unique constraint column. This means that the roll_number column can contain only distinct values.

Let's see the syntax:

Modify UNIQUE Constraints

In SQL Servers, we do not have any direct statement to modify a UNIQUE constraint. Therefore, we need to first drop the constraint and then recreate the constraint that we want to change.

Conclusion

UNIQUE constraint is a column-level constraint used to ensure that the constrained column should only contain a unique value in the specified column.
It can contain a null value. UNIQUE Constraint is usually used to identify each tuple in the table uniquely. The syntax of imposing a unique constraint at the table creation is: CREATE TABLE table_name (column_name data_type UNIQUE);.
The syntax of imposing unique constraints after table creation is: ALTER TABLE table_name MODIFY column_name data_type UNIQUE;.
The syntax of dropping or removing unique constraint from column(s) of a table is: ALTER TABLE table_name DROP INDEX column_name;.
We can also add UNIQUE constraint on column(s) of an existing column. The syntax is: ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE(column_name);.
In SQL Servers, we do not have any direct statement to modify a UNIQUE constraint, therefore, we need to first drop the constraint and then recreate the constraint that we want to change.