Category Archives: Keys and Constraint

Difference between a Primary Key and a Unique Constraint – DBA Perspective

I was preparing for an interview (hope my manager isn’t reading this yet) & found this wonderful link SQL Server DBA Interview Questions which had a great question on the difference between a Primary Key and a Unique Constraint.

That question brought in a thought about how this difference between a Primary Key and a Unique Constraint matters to a DBA? (Developer’s headache – not mine was my first opinion, however that quickly changed when I started exploring it.)

For DBAs like me who might NOT remember all the differences, this simple post by Shailendra Chauhan captures the essence of the Difference between Primary Key and Unique Key

Here is the DBA perspective that DBAs would hopefully benefit from {or pay the price if ignored 😉 }

Primary Key creates a Clustered Index internally, where Unique Constraint creates a “Unique, Non-Clustered” Index.

This means you can actually have multiple Unique Constraints in a single table. Simple yet useful to maintain Data Consistency.

For all those Science fans who live by “Seeing is believing”, here’s the screen preview:

Unique_1

Notice that I have created a Unique Constraint “IX_ID” on a Column “ID”. SQL Server has created a “Unique, Non-Clustered” Index with the same name.

Key Takeaway here would be to consider the above difference in how SQL server perceives your Primary key/Unique Constraint. Based on this solid understanding, you can make your database design better!

Cheerio! Hope this article was useful 🙂

PS: Please feel free to add onto your inputs/experience in the below comments section.

Leave a comment

Filed under Keys and Constraint, sql server 2005, sql server 2008, sql server 2012, Uncategorized