Difference between UNIQUE constraint and PRIMARY key
A UNIQUE constraint is similar to PRIMARY key, but you can have more than one UNIQUE constraint per table.
When you declare a UNIQUE constraint, SQL Server creates a UNIQUE index to speed up the process of searching for duplicates. In this case the index defaults to NONCLUSTERED index, because you can have only one CLUSTERED index per table.
* The number of UNIQUE constraints per table is limited by the number of indexes on the table i.e 249 NONCLUSTERED index and one possible CLUSTERED index.
Contrary to PRIMARY key UNIQUE constraints can accept NULL but just once. If the constraint is defined in a combination of fields, then every field can accept NULL and can have some values on them, as long as the combination values is unique.
Categories: Database, SQL Server
Leave a reply to Kanhaiya Cancel reply
Recent Comments
 | Nate on Stored Procedures are EVI… |
 | dhirendra kumar dhir… on How do you find the Second hig… |
| dhirendra kumar dhir… on How do you find the Second hig… |
 | shivani pandit on Indexes in SQL Server |
| shivani pandit on Difference between UNIQUE cons… |
MS SQL Server Tutorials 
The basic differences between Primary Key and Unique key are as follows.
1) By default Primary Key will generate Clustured Index
whereas Unique Key will Generate Non-Clustured Index.
2) Primary Key is a combination of Unique and NOT NULL Constraints so it can’t
have duplicate values or any NUll
Whereas for Oracle UNIQUE Key can have any number of NULL whereas for SQL
Server It can have only one NULL
3) A table can have only one PK but It can have any number of UNIQUE Key.
thank you ,very helpful
I THINK IT IS RIGHT
thank you
a comparison to not null key would also be helpful
It is good try to collect more points
Hi,
Good explanation
Very clear and helpful explanation! Thanks a lot
good
If possible,pls include a detailed explanation…
Primary Key does not allow null values where as unique constraint allow ‘single’ null value.
A table can have only single Primary Key where as it can have multiple unique constraints ( max 16)
thanks a lot. i was looking for this info….
Very good. Many thanks
Very good explanation difference between primary key and unique key
its very good article..
thks a lot
thks a lot to Rubinaalexmary
regards
achiever.
Simly Great….
excellent, thanks a lot….
ty so much
Nice arctical ……
its good, but pls try to give the detailed description about the differences
Good explanation..
txs
nirmala
good explanation
Are govind chuudaraa
jaheer gaadiki kuuda cheppu
Helped a lot..
nice, useful for getting knowledge from the scratch.
Please give an example.
thanks, very good explanation
Very Well Explanation.
Thanks for your help
Very nice explanation
Its very good and very clear explanation. Thank you so much
thnx
nice explaination…..
thnx
nic explaination…..
nice xplanation!!!
good!! but….. need more explanation.
Thanx a lot….
Unique key constrain nothing but when we apply unique key column on any particular table or column, that column does not accept duplicate value, it can accept Null values if we cross the condition on column it will an error message,
but we can have more than one UNIQUE constraint per table.
Primary key nothing but not null constraint and unique column when we apply primary key column on any particular table or column, that column can not accept null values and it can not accept duplicate values mean time it arranges the data in ascending order,
but we can not have more than one primary key constraint per table
thank you….
thanks krishna..!
very Good Explanation helps a lot…
thank u
A good explaination. not messy with so many explanations and examples. as the first level of explanation. Good and summarised. Thanks
This is one of the best answer so far, I have read online. Just useful information. Very well presented. I had found another good collection of sql keys over internet.
please check out this link…
http://mindstick.com/Articles/d837d16c-d483-4948-ad18-41b405c011bd/?Important%20SQL%20Keys
good explanation thaq.
can any one help me to find out how to delete a duplicat values in a table
what is cluster index and non cluster index?
Very well Explained…Thank You !
Hi Guys,
Please let me know,which one is the faster while retrieving the data.
thanks was helpfull
Thanks it was helpful
nice
thanks was helpfull
thanx its helpful