SQL Constraints: Unique Constraint

The unique constraint in SQL is used to ensure that the values in a column or a set of columns are unique. This constraint is used to maintain the integrity of data in a table by preventing the insertion of duplicate records.

Syntax

The syntax for adding a unique constraint to a table is as follows:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column_name);

Example

Consider the following example where we want to add a unique constraint to the "employees" table on the "employee_id" column:

ALTER TABLE employees
ADD CONSTRAINT emp_id_unique UNIQUE (employee_id);

Output

If the above query is executed successfully, the output will not contain any rows. However, if the same value is inserted into the "employee_id" column again, it will result in an error stating that the unique constraint has been violated.

Explanation

The unique constraint allows for the insertion of only unique values in a column or a set of columns. Multiple can be combined to create a composite unique constraint.

A unique constraint can be added to a table when creating the table or afterwards using the ALTER TABLE command.

Use

The unique constraint can be used in scenarios where duplicate values in a column or a set of columns are not desired. Some common examples include:

• Ensuring that email or username is unique in a user database table
• Preventing duplicate product names in an inventory table
• Avoiding the insertion of multiple records with the same ID in a table

Important Points

• The unique constraint ensures that the values in a column or a set of columns are unique
• The constraint can be added to a table using the ALTER TABLE command
• A unique constraint can be used to prevent the insertion of duplicate data in a table
• Multiple columns can be combined to create a composite unique constraint

Summary

In summary, the unique constraint is used in SQL to maintain the integrity of data in a table by ensuring that the values in a column or a set of columns are unique. It can be added to a table using the ALTER TABLE command and is useful in scenarios where duplicate values are not desired.