---

UNIQUE constraint ensures that a field or column will only have unique values. A UNIQUE constraint field will not have duplicate data. This constraint can be applied at column level or table level.

Using UNIQUE constraint when creating a Table (Table Level)

Here we have a simple CREATE query to create a table, which will have a column s_id with unique values.

Example:

Code:

CREATE TABLE Student(
s_id int NOT NULL UNIQUE,
Name varchar(60),
Age int
);

The above query will declare that the s_id field of Student table will only have unique values and wont take NULL value.

Using UNIQUE constraint after Table is created (Column Level)

Example:

Code:

ALTER TABLE Student ADD UNIQUE(s_id);

The above query specifies that s_id field of Student table will only have unique value.

SQL Server

We use UNIQUE constraint to enforce uniqueness of a column i.e the column shouldn't allow any duplicate values. We can add a Unique constraint thru the designer or using a query.

To add a unique constraint using SQL server management studio designer:

• Right-click on the table and select Design
• Right-click on the column, and select Indexes/Keys...
• Click Add
• For Columns, select the column name you want to be unique.
• For Type, choose Unique Key.
• Click Close, Save the table.

To create the unique key using a query:

Alter Table Table_Name
Add Constraint Constraint_Name Unique(Column_Name)

Both primary key and unique key are used to enforce, the uniqueness of a column. So, when do you choose one over the other?

A table can have, only one primary key. If you want to enforce uniqueness on 2 or more columns, then we use unique key constraint.

What is the difference between Primary key constraint and Unique key constraint? This question is asked very frequently in interviews.

1. A table can have only one primary key, but more than one unique key
2. Primary key does not allow nulls, where as unique key allows one null

To drop the constraint

1. Right click the constraint and delete. Or
2. Using a query

   
   Alter Table tblPerson
   Drop COnstraint UQ_tblPerson
   
   

---