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:
1. Right-click on the table and select Design
2. Right-click on the column, and select Indexes/Keys...
3. Click Add
4. For Columns, select the column name you want to be unique.
5. For Type, choose Unique Key.
6. 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_Email
To add a unique constraint using SQL server management studio designer:
1. Right-click on the table and select Design
2. Right-click on the column, and select Indexes/Keys...
3. Click Add
4. For Columns, select the column name you want to be unique.
5. For Type, choose Unique Key.
6. 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_Email
can we create foreign key on unique key in sql?
ReplyDeleteAs far as I understand: NO, bcoz unique key column holds unique values just like primary key coulmn BUT FK is a subset of the values of the PK in the other table and hence can be duplicated.
DeleteWouldn't it be possible to do it if the number of entries in the table referenced by the FK is greater than those in the first table? Theoretically,I mean.
DeleteYes, You can create foreign key on Unique key with the below conditions
Delete-- We should forget the definition "Foreign key allows duplicate values and OK them to contain multiple null values "
Since foreign key on unique column will allow only Unique values including only one null.
Iam sure it is possible to create a primary key for multiple column at a same time while creating a table..
ReplyDeleteI don't think so, it will say "Cannot add multiple primary key constraints to the table".
DeleteYes, multiple columns combined can be primary key. Then the combination will be checked for uniqueness.
DeleteCan we insert multiple null values in unique key ,then how it enforces uniqueness
ReplyDeleteWe cannot insert multiple null values in unique key column,
DeleteUnique key allows only one null value.
may i know why unique key wont allow multiple null values
ReplyDeleteBecause,two null values are same like duplicate..Unique key will not allow duplicate values.you enter two null values it must be duplicate..that's why unique key does not allow duplicate values..
DeleteBut, How to NULL values will be same. They are unknown values. SO, how we can say that they are same.
DeleteNull has its specific value, that's why database can determine duplicate
Deleteyeah.. SQl serevr will not allow this.Other DB may
ReplyDeleteOracle allows so
ReplyDeletei have id as primary key in users table and GUID is another id for uniquness of column. How can i make GUID unique in my table that can be auto generate in the database?
ReplyDelete