SQL Server - How does Unique Constraint work?

Constraints are the rules and restrictions to apply on the type of data in a table. They specify the limit on the type of data that can be stored in a particular column in a table using constraints and also maintain the data integrity and accuracy in the table. They also ensure the unwanted data can't be inserted into tables.

The basic funda is that A Not null constraint restrict the insertion of null values into a column. 

Unique Constraint - It is another feature of SQL which ensures that each row for a column must have a unique value and provides an uniqueness identity of each row in the table. It is similar like a Primary key but it can accept only one null value.

How can we create Unique constraints?

In a table one or more column can contain a Unique Constraint. We can create UNIQUE constraints at two levels -

Column Level Unique Constraints - We can define Unique constraint with CREATE TABLE statement or in time with table definition. 

For example, the below query creates a table Employee with the column fields EmpId, FirstName, LastName and Age as NOT NULL but MobileNo column with Unique constraint. Thus, MobileNo column may have only one null value but for other rows, this value cannot be duplicate whenever we are going to insert/update the data into this table.

---- Column Level Unique Constraints CREATE TABLE TBL_EMPLOYEE ( EmpId int NOT NULL, FirstName varchar(25) NOT NULL, LastName varchar(25) NOT NULL, Age int NOT NULL, MobileNo Bigint Unique ) ---- Insert values into table INSERT INTO TBL_EMPLOYEE (EmpId, FirstName, LastName, Age, MobileNo) VALUE (101,’Ryan’,’Arjun’,22,7856124580)

Keep in your mind  -

• You can not alter constraints ever but you can drop them and then recreate.
• If you are not going to assign a name for unique constraint then system will generate a static constraint name for the unique column. 

Table Level Unique Constraints -

You can create a Unique constraint after creating the table with the help of Alter Table command as given below.

For example,

---- Table Level - Unique being added to another column ALTER TABLE TBL_EMPLOYEE ADD EmaiId Varchar(50) Constraint  UQC_Email Unique ---- drop Unique Constraint ALTER TABLE TBL_EMPLOYEE Drop Constraint  UQC_Email

Key Points - Before applying unique constraints to your table, please make sure that you can apply them to only those columns which have the similar feature to Primary Key. For example - mobile number, email Id etc.
For a demo, please visit us at YouTube -