The UNIQUE key constraint in SQL Server ensures that all values in a column or a combination of columns are distinct. It is used to prevent duplicate values in specified columns while allowing one NULL value per column in most cases (unlike the PRIMARY KEY constraint, which does not allow NULLs).

Key Features of UNIQUE Key

Uniqueness: The primary purpose is to enforce the uniqueness of the values in the column(s).

NULLs Allowed: UNIQUE constraints permit a single NULL in the column (if the column is nullable), as NULL is not considered equal to another NULL.

Multiple UNIQUE Constraints: A table can have multiple UNIQUE constraints, unlike a PRIMARY KEY, which is limited to one per table.

Indexed: When a UNIQUE constraint is created, SQL Server automatically creates a unique index on the column(s) to enforce the constraint efficiently.

Syntax

To create a UNIQUE key constraint during table creation:

CREATE TABLE table_name (
    column1 data_type,
    column2 data_type UNIQUE, -- Single-column unique constraint
    column3 data_type,
    CONSTRAINT unique_name UNIQUE (column1, column3) -- Multi-column unique constraint
);

To add a UNIQUE key constraint to an existing table:

ALTER TABLE table_name
ADD CONSTRAINT unique_name UNIQUE (column_name);

Removing a UNIQUE Constraint

To drop a UNIQUE constraint, you need to know its name:

ALTER TABLE table_name
DROP CONSTRAINT unique_name;

Differences Between PRIMARY Key and UNIQUE Key

Feature	PRIMARY Key	UNIQUE Key
Number per table	Only one	Multiple allowed
NULL values	Not allowed	One NULL value allowed per column
Default index type	Clustered	Non-clustered (can be changed)

Practical Use Cases

Email addresses in a user table: Ensure no two users have the same email.

CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    Email NVARCHAR(255) UNIQUE
);

Composite keys for uniqueness: Ensure a combination of FirstName and LastName is unique.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName NVARCHAR(100),
    LastName NVARCHAR(100),
    CONSTRAINT unique_name UNIQUE (FirstName, LastName)
);

By enforcing the UNIQUE key constraint, SQL Server helps maintain data integrity and prevents unintended duplicates.