What is difference between primary key and unique key in table - SQL database

primary key vs unique key in SQL
The primary key and unique key are two important concepts in a relational database and used to uniquely identify a row in a table. Both primary key and unique key 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 questions along with classics like truncate vs delete and  How to manage transaction in a database, mostly asked to fresher and 2 to 3 years experience guys in any programming language. SQL is not just limited to any DBA or PLSQL developer but its an important skill even for Java programmer and you can expect SQL interview question 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 difference between a primary and foreign key or 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. 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 also used to link two tables when you are joining them. Btw, there is a slight difference in unique key and unique key constraint, former is a concept while later is an implementation which ensures that no duplicate key is inserted into your tables. 

Btw, If you are new to these concepts, I suggest you to first read Head First SQL or join Introduction to SQL, a free course on Pluralsight website to get a full understanding of essential SQL and database concepts.



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 is some 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 primary key can be a combination of more than one unique keys in the same table.

3) There can be only one primary key per table in relation database e.g. MySQL, Oracle or Sybase but there can be more than one unique key per table.

4) The unique key is represented using unique constraint while a primary key is created using primary key constraint in any table and it's automatically got unique constraint. See Introduction to SQL by Jon Flanders, a free course from Pluralsight for more details on how unique key constraint works.

Difference between Primary key and Unique key in SQL


5) Many database engines automatically put 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 same time. This is true for both Oracle and MySQL Server. If you want to learn more, you can check Oracle Database 12c Fundamentals course from Pluralsight, it's free if you sign-up for a 10-day trial.


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 relation database e.g. Oracle, MySQL, and SQL Server:

Difference between primary and unique key in table SQL


These were some of the difference between primary key and unique key in SQL or any table. It's'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.


Other SQL resources you may like
The Complete SQL Bootcamp Become an expert at SQL!
What is difference between correlated and non-correlated subquery in SQL
10 ways to use SELECT queries in SQL
How to connect Java program to Oracle database
List of frequently used MySQL commands for quick reference
200+ SQL Interview Questions

P.S. - If you are looking for online training/course to learn SQL from scratch, I suggest you joining Introduction to SQL by Jon Flanders. It's one of the best sources to learn SQL fundamentals e.g. join, subquery, aggregate functions, window functions, grouping data, advanced filtering and SQL query optimization.

4 comments:

  1. Here are few more differences between primary and unique key :

    1) Primary key by default creates clustered index while Unique key creates nonclustered index.

    2) Only one Primary key per table but you can have as many Unique key as you need.

    ReplyDelete
  2. 1. Behavior: Primary Key is used to identify a row (record) in a table whereas Unique-key is to prevent duplicate values in a column.

    2. Indexing: By default Sql-engine creates Clustered Index on primary-key if not exists and Non-Clustered Index on Unique-key.

    3. Nullability: Primary key does not include Null values whereas Unique-key can.

    4. Existence: A table can have at most one primary key but can have multiple Unique-key.

    5. Modifiability: You can’t change or delete primary values but Unique-key values can.

    ReplyDelete
  3. Abhishek Kumar...your analysis is to focused to the point and simple and easily understood by everyone ...thanks

    ReplyDelete