In SQL Server, we have two keys that distinctively or uniquely identify a record in the database. Both the keys seem identical, but actually, both are different in features and in behaviors. In this article, I would like to share the key differences between the primary key and the unique key.
For more help about keys in SQL Server refer to the article Different Types of SQL Keys.
What is the primary key?
The primary key is the values of the certain column that uniquely identifies any row of a table. The primary key cannot have NULL and duplicate values. The primary key is used to add integrity to the specific table as we have a table with unique rows.
While using the primary key with the table, both duplicate and NULL values are not valid for the columns. And, it can be utilized as foreign keys for other different tables accordingly.
What is a unique key?
A unique key is a unique value amongst other values that are used to protect duplication of the values in a column of the table. The primary use of a unique key in a table is to prevent duplicate values. But, when it comes to the unique values, the primary key also includes them. So, there is one big difference that makes a unique key different, and it is that the unique key may have a NULL as a value but the primary key does not allow NULL as a value.
Difference between Primary Key and Unique Key
Defining Primary key and Unique key in SQL Server
CREATE TABLE Employee ( EmpID int PRIMARY KEY, --define primary key Name varchar (50) NOT NULL, MobileNo int UNIQUE, --define unique key Salary int NULL )Read More Articles Related to sqlserver
Even though both primary and unique key has a few set of similarities, certainly, primary and unique keys have significant differences such as we can only have one primary key per table, but we are allowed to have multiple unique keys. Same way, a primary key column does not accept NULL values, whereas the unique key columns may contain one null value with each column. And most importantly, the primary key column has a unique clustered index whereas a unique key column has a unique non-clustered index.
I hope you will enjoy these tricks while programming with SQL Server. I would like to have feedback from my blog readers. Your valuable feedback, question, or comments about this article are always welcome.