UNIQUE KEY constraints in SQL
Table of Content:
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.
- A table can have only one primary key, but more than one unique key
- Primary key does not allow nulls, where as unique key allows one null
To drop the constraint
- Right click the constraint and delete. Or
- Using a query
Alter Table tblPerson Drop COnstraint UQ_tblPerson