UNIQUE Key Constraint

A Unique Key is a combination of one or more than one columns of a table that uniquely identify a record in a table. Unique Key can also be used as foreign keys for another table.

Both Primary Key and Unique Key are used to uniquely identify a record in a table. So Lets discuss the differance between Primary Key and Unique Key

1. A table can have only one Primary Key while there can be multiple Unique Keys in the table.

2. Primary Key will not accept NULL values whereas Unique Key can accept one NULL value per column.

3. By default database engine creates Clustered Index on Primary Key and Non-Clustered Index on Unique-key.

Example

1. Add a Unique constraint on a column 

     CREATE TABLE TblStudent
     (
            SRNo VARCHAR(15) UNIQUE,
            StudentName VARCHAR(50),
            FatherName VARCHAR(50),
            CourseCode VARCHAR(15)
     )

2. Add a Unique constraint on a new table

     CREATE TABLE TblStudent
     (
            SRNo VARCHAR(15),
            StudentName VARCHAR(50),
            FatherName VARCHAR(50),
            CourseCode VARCHAR(15),
            CONSTRAINT UQ_TblStudent_SRNo UNIQUE(SRNo)
     )

3. Add a Unique constraint on a existing table

When you add a Unique Key to an existing column in the table, Then the Database Engine make sure that all existing values into that column should be unique. If you add a Unique Key to a column which have duplicate values, the Database Engine will not add the Unique Key and throw an error.

    ALTER TABLE TblStudent
    ADD CONSTRAINT UQ_TblStudent_SRNo UNIQUE (SRNo); 

4. Add a Unique constraint on multiple columns in an existing table

    ALTER TABLE TblStudent
    ADD CONSTRAINT UQ_TblStudent_SRNo_CourseCode UNIQUE (SRNo,CourseCode); 

5. Modify a Unique constraint

You can not directly modify a UNIQUE constraint. First you need to delete the existing UNIQUE constraint and then re-create it with the new definition.

6. Delete a unique constraint

Given query return the name of unique constraint.

    SELECT name  
    FROM sys.objects  
    WHERE type = 'UQ' AND OBJECT_NAME(parent_object_id) = N'TblStudent'; 

The below query will delete the unique constraint.

    ALTER TABLE TblStudent   
    DROP CONSTRAINT UQ_TblStudent_SRNo_CourseCode;