Saturday, October 20, 2012

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

primary key vs unique key in SQL
primary key and unique key are two important concept in 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 are 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 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 confuse between foreign key and unique key, which is primary key of other table in relation.


Difference between primary key and unique key in SQL

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


1) Unique key in a table can be null, at-least one but primary key can not be null in any table in relation database like MySQL , Oracle etc.

2) Primary key can be combination of more than one unique keys in 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) Unique key is represented using unique constraint while primary key is created using primary key constraint in any table and it's automatically gets unique constraint.

5) Many database engine automatically puts clustered index on primary key and since you can only have one clustered index per table, its not available to any other unique key at same time.

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

Other SQL articles you may like
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
What is mysqldump utility

1 comment:

  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

Java67 Headline Animator