SQL UNIQUE Constraint

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.

Example

#

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.


Add UNIQUE

#

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 (Email, Phone)

The combination of these columns is unique -- not the individual columns.


Remove UNIQUE

#

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.


You may also like



Guides


vsn 3.1