What is a unique constraint?

A UNIQUE constraint is a fundamental SQL concept used to ensure that all values in a column or a group of columns are distinct. It prevents duplicate entries in the specified columns, thereby maintaining data integrity within a database table.

What is a UNIQUE Constraint?

A UNIQUE constraint is a rule applied to one or more columns in a database table to ensure that every value in that column (or combination of columns) is distinct. No two rows can have the same value for the constrained column(s). This is similar to a PRIMARY KEY, but a table can have multiple UNIQUE constraints, whereas it can only have one PRIMARY KEY.

Key Characteristics

Here are the key characteristics of a UNIQUE constraint:

• Ensures uniqueness of values in the specified column(s).
• A table can have multiple UNIQUE constraints.
• Allows NULL values, but only one NULL value per constrained column (as NULL is generally considered distinct from another NULL for this purpose in many SQL databases).
• Can be applied to a single column or a combination of columns (composite unique constraint).
• Automatically creates an index on the constrained columns for faster data retrieval and enforcement.

Syntax for Creating a UNIQUE Constraint

UNIQUE constraints can be defined when creating a table or added to an existing table.

Defining at Table Creation

CREATE TABLE Customers (
    CustomerID INT NOT NULL UNIQUE,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100) UNIQUE
);

In this example, CustomerID and Email columns are defined with UNIQUE constraints directly during table creation.

Defining with a Named Constraint

CREATE TABLE Products (
    ProductID INT NOT NULL,
    ProductName VARCHAR(100) NOT NULL,
    ProductSKU VARCHAR(50) NOT NULL,
    CONSTRAINT UC_ProductSKU UNIQUE (ProductSKU)
);

Naming the constraint (UC_ProductSKU) makes it easier to refer to when altering or dropping it later.

Adding to an Existing Table

ALTER TABLE Employees
ADD CONSTRAINT UC_EmployeeID UNIQUE (EmployeeID);

This statement adds a UNIQUE constraint to the EmployeeID column in the Employees table.

Composite UNIQUE Constraint

CREATE TABLE Orders (
    OrderID INT NOT NULL,
    ProductID INT NOT NULL,
    OrderDate DATE,
    CONSTRAINT UC_OrderProduct UNIQUE (OrderID, ProductID)
);

Here, the combination of OrderID and ProductID must be unique. For instance, (1, 101) is allowed once, but (1, 101) cannot appear again. However, (1, 102) and (2, 101) are both allowed.

Differences from PRIMARY KEY

Benefits

• Data Integrity: Prevents the insertion of duplicate values, ensuring the quality and reliability of data.
• Improved Performance: Automatically creates an index, which can speed up data retrieval operations involving the constrained columns.
• Alternative Keys: Can serve as an alternative candidate key for identifying records, useful for foreign key references if not using the primary key.