SQL Server/T-SQL Tutorial/Constraints/Unique

Adding a UNIQUE Constraint to an Existing Table

The syntax is as follows:
ALTER TABLE table_name
ADD CONSTRAINT  constraint_name
UNIQUE  (column [ ASC | DESC ] [ ,...n ] )

Creating a UNIQUE Constraints when creating a table

5>
6>    CREATE TABLE Shippers
7>    (
8>       ShipperID     int    IDENTITY    NOT NULL
9>          PRIMARY KEY,
10>       ShipperName   varchar(30)        NOT NULL,
11>       Address       varchar(30)        NOT NULL,
12>       City          varchar(25)        NOT NULL,
13>       State         char(2)            NOT NULL,
14>       Zip           varchar(10)        NOT NULL,
15>       PhoneNo       varchar(14)        NOT NULL
16>          UNIQUE
17>    )
18>    GO
1>
2>    drop table Shippers;
3>    GO

Creating UNIQUE Constraints on Existing Tables

4>
5>
6>    CREATE TABLE Employees
7>    (
8>       EmployeeID       int           IDENTITY  NOT NULL,
9>       FirstName        varchar(25)             NOT NULL,
10>       MiddleInitial    char(1)                 NULL,
11>       LastName         varchar(25)             NOT NULL,
12>       Title            varchar(25)             NOT NULL,
13>       SSN              varchar(11)             NOT NULL,
14>       Salary           money                   NOT NULL,
15>       PriorSalary      money                   NOT NULL,
16>       LastRaise AS Salary - PriorSalary,
17>       HireDate         smalldatetime           NOT NULL,
18>       TerminationDate  smalldatetime           NULL,
19>       ManagerEmpID     int                     NOT NULL,
20>       Department       varchar(25)             NOT NULL
21>    )
22>    GO
1>
2>    ALTER TABLE Employees
3>       ADD CONSTRAINT AK_EmployeeSSN
4>       UNIQUE (SSN)
5>    GO
1>
2>    drop table Employees;
3>    GO

Unique constraint

2> CREATE TABLE customer_location
3> (
4> cust_id                 int    NOT NULL,
5> cust_location_number    int    NOT NULL,
6> CONSTRAINT customer_location_unique UNIQUE
7>     (cust_id, cust_location_number)
8> )
9> GO
1>
2> EXEC sp_helpconstraint customer_location
3> GO
Object Name

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
customer_location


constraint_type                                                                                                                                    constraint_name
                                                                            delete_action update_action status_enabled status_for_replication constraint_keys





-------------------------------------------------------------------------------------------------------------------------------------------------- -----------------------------------------------------
--------------------------------------------------------------------------- ------------- ------------- -------------- ---------------------- ----------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------
UNIQUE (non-clustered)                                                                                                                             customer_location_unique
                                                                            (n/a)         (n/a)         (n/a)          (n/a)                  cust_id, cust_location_number






No foreign keys reference table "customer_location", or you do not have permissions on referencing tables.
1>
2> drop table customer_location;
3> GO

Unique Constraint: Enforce uniqueness on a column which is not the column used to primarily identify each row.

5> CREATE TABLE Employee (
6>    EmployeeID Int IDENTITY(1,1) NOT NULL
7>   , LastName nVarChar(50) NOT NULL
8>   , FirstName nVarChar(50) NOT NULL
9>   , SSN Char(9) NOT NULL)
10> GO
1>
2>
3> drop table Employee;
4> GO
1>

Unique constraints can be created when initially creating the table or added after.

7> CREATE TABLE Employee (
8>    EmployeeID Int IDENTITY(1,1) NOT NULL
9>   , LastName nVarChar(50) NOT NULL
10>   , FirstName nVarChar(50) NOT NULL
11>   , SSN Char(9) NOT NULL
12>   , CONSTRAINT U_SSN UNIQUE NONCLUSTERED (SSN))
13> GO
1> ALTER TABLE Employee
2> ADD CONSTRAINT U_SSN UNIQUE NONCLUSTERED (SSN)
3> GO
1> drop table Employee;
2> GO

Unique constraints will also allow one NULL, whereas primary keys will not.

Unique constraints can be created when initially creating the table or added after.
7> CREATE TABLE Employee (
8>    EmployeeID Int IDENTITY(1,1) NOT NULL
9>   , LastName nVarChar(50) NOT NULL
10>   , FirstName nVarChar(50) NOT NULL
11>   , SSN Char(9) NOT NULL
12>   , CONSTRAINT U_SSN UNIQUE NONCLUSTERED (SSN))
13> GO
1> ALTER TABLE Employee
2> ADD CONSTRAINT U_SSN UNIQUE NONCLUSTERED (SSN)
3> GO
1> drop table Employee;
2> GO