SQL 2005+

SQL Server Unique Constraints

by Richard Carr, published at http://www.blackwasp.co.uk/SQLUnique.aspx

The fourteenth part of the SQL Server Programming Fundamentals tutorial discusses unique constraints. This type of constraint adds a rule to a table to ensure that no two rows contain duplicate information within the constrained columns.

Adding a Unique Constraint Using Transact-SQL

Unique constraints can be added to a table using T-SQL when creating a table or at a later time. As we are working against the JoBS tutorial database, there is no requirement to add a new table with a unique constraint. However, if you wanted to create the GeographicalAreas table including the constraint created using SSMS in the previous section, you could use the following SQL statement:

CREATE TABLE GeographicalAreas
(
    AreaCode CHAR(3) NOT NULL,
    AreaName VARCHAR(25) NOT NULL
    CONSTRAINT PK_GeographicalAreas PRIMARY KEY CLUSTERED 
    (
        AreaCode
    ),
    CONSTRAINT UQ_GeographicalAreaName UNIQUE NONCLUSTERED
    (
        AreaName
    )
)

As you can see, the new constraint uses a similar syntax to that for primary keys. The CONSTRAINT clause is followed by the name of the new constraint and the UNIQUE clause to indicate that it is a unique constraint that is being created. The type of index is specified using either CLUSTERED or NONCLUSTERED. Finally, the columns of the constraint are added in parentheses (). Note that the two constraints are comma-separated.

To add a unique constraint to an existing table using T-SQL, the ALTER TABLE and ADD clauses are combined with the new constraint specification. The syntax of the constraint definition is identical to that used above. To demonstrate, let's add a new unique constraint to the Parts table. This constraint will ensure that the part name cannot include duplicate values:

ALTER TABLE PARTS
ADD CONSTRAINT UQ_PartName UNIQUE NONCLUSTERED
(
    PartName
)

If you wish to create an alternate key that contains more than one column, the column names should be comma-separated. For example, if we want to ensure that a customer's address is unique, we need to combine six columns in the same unique constraint, as follows:

ALTER TABLE CustomerAddresses
ADD CONSTRAINT UQ_CustomerAddress UNIQUE NONCLUSTERED
(
    Address1, Address2, Address3, TownOrCity, AreaCode, Postcode
)

JoBS Database Unique Constraints

The remainder of the unique constraints can now be added to the JoBS tutorial database. Use either the graphical user interface tools or T-SQL to add the following items.