A UNIQUE constraint specifies that the values in a column must be unique.
This constraint ensures that invalid data does enter the column.
A UNIQUE constraint can be applied to multiple columns (see below).
To enforce uniqueness, SQL Server creates a UNIQUE Index on the column.
In this table each Email value must be unique.
CREATE TABLE Customer (
Id INT IDENTITY,
FirstName NVARCHAR(50) NOT NULL,
LastName NVARCHAR(50) NOT NULL,
Email NVARCHAR(50) UNIQUE NOT NULL,
Phone NVARCHAR(25)
)
Email is unique and it cannot be NULL in this example.
To add a unique column constraint to an existing table use ALTER TABLE.
ALTER TABLE Customer
ADD UNIQUE (Phone)
This adds uniqueness to the Phone column to the Customer table.
In this example a combination of two columns must be unique.
ALTER TABLE Customer
ADD CONSTRAINT UQ_Customer UNIQUE (LastName, Phone)
The combination of these columns is unique -- not the individual columns.
To remove a UNIQUE constraint use ALTER TABLE.
ALTER TABLE Customer
DROP CONSTRAINT UQ_Customer
This does not remove any columns, only the validation of these columns.
