Difference between Primary key Unique key in Database SQL

Hello friends, both primary key and unique key are two important concepts in a relational database which are used to uniquely identify a row in a table. For example, EmployeeId is often primary key in Employee table which can uniquely identify any employee which is denoted by a row in Employee table. Similarly, a employee can also be identify with EmployeeName if there is no duplicate but if its not primary key then we call it a unique key. While both primary key and unique keys can identify a row uniquely but there is some subtle difference between them which we will see in this article. 

In fact, primary key vs unique is a popular SQL interview question along with classics like truncate vs delete vs drop and difference between char vs varchar data type, both are mostly asked to fresher and 2 to 3 years experience guys in Software Development or Data Analysis. 

By the way, SQL is not just limited to any DBAs, Data Analyst, Data Scientist or PLSQL developer but it's an important skill even for Java programmers and you can expect SQL interview questions even in many Java interviews.

Some time programmer also confuses between a unique key and a foreign key, which is the primary key of other tables in relation, hence questions like the difference between a primary and foreign key or a primary and unique key, or foreign and unique keys are asked to check their understanding.

The key thing to remember is that they help to keep data integrity in check. The unique key ensures that no duplicate values are inserted in the database, while a primary and foreign key is responsible for maintaining referential integrity.

They are also used to link two tables when you are joining them using join clause. 

By the way, there is a slight difference in unique key and unique key constraint, former is a concept while the latter is an implementation that ensures that no duplicate key is inserted into your tables.

Difference between primary key and unique key in SQL

As I said both primary and unique key uniquely identifies each row in the table but there are subtle difference between them. here are some of them :

1. Unique key in a table can be null, at least one but the primary key cannot be null in any table in a relational database like MySQL, Oracle, etc.

2. The unique key is represented using a unique constraint while a primary key is created using a primary key constraint in any table and it's automatically got a unique constraint.

3. The primary key can be a combination of more than one unique keys in the same table.

4. There can be only one primary key per table in relation to databases like MySQL, SQLLite, Oracle, PostgreSQL, SQLServer, or Sybase but there can be more than one unique key per table.

5. Many database engines automatically put a clustered index on the primary key and since you can only have one clustered index per table, it's not available to any other unique key at the same time. This is true for both Oracle and MySQL Server

5. Primary key can be made foreign key into another table also.

Here is a nice summary of the difference between a primary key and a unique key in a table. These differences are valid on most of the relational databases like Oracle, MySQL, and SQL Server:

That's all about difference between primary key and unique key in database. While these differences are for primary key and unique key, they also applicable to primary key and unique key constraint which is used to declare them. Its's one of those SQL interview questions which you don't like to miss before going for any programming interview or any database, SQL interview. I have myself asked this question many times. 

Other SQL Tutorials and Articles you may like

• How to find top 10 Records in SQL
• Difference between UNION and UNION ALL in SQL
• How to convert String to Date in SQL?
• 4 Examples of CASE expression in SQL Server
• Does order of columns matter in Composite index?
• Difference between Self and Equi join in SQL
• 10 Example of SELECT queries in SQL
• How to create and rollback transaction in database?
• MySQL vs NoSQL comparison
• How to use Stored Procedure in SQL?
• 10 Examples of ALTER command in SQL
• How to use WHERE and HAVING clause in SQL

Thank you for reading this article so far. If you have any questions feel free to ask in comments.