PRIMARY Vs. UNIQUE Key

What is the difference between PRIMARY Key and UNIQUE Key?

Primary key and unique are Entity integrity constraints

• Primary key allows each row in a table to be uniquely identified and ensures that no duplicate rows exist and no null values are entered.
• Unique key constraint is used to prevent the duplication of key values within the rows of a table and allow null values. (In oracle, one null is not equal to another null).

1) Unique key in a table can be null, at-least one but primary key can’t 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 engines automatically put clustered index on 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.

SQL> DESC TEMPTABLE;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER
 NAME                                               VARCHAR2(20)
 PHONENO                                            VARCHAR2(20)

SQL> INSERT INTO TEMPTABLE Values(101,'Ravi','9021150300');

1 row created.

SQL> INSERT INTO TEMPTABLE Values(102,'Siva','7221150300');

1 row created.

SQL> /*Now Try to insert ID as NULL*/

SQL> INSERT INTO TEMPTABLE Values(NULL,'Jeevan','9412368279');
INSERT INTO TEMPTABLE Values(NULL,'Jeevan','9412368279')
                             *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("HR"."TEMPTABLE"."ID")

SQL> /*Now Insert PhoneNo as NULL*/

SQL> INSERT INTO TEMPTABLE Values(103,'Jeevan',NULL);

1 row created.

SQL> /*Insert more NULL Values in Unique Key*/

SQL> INSERT INTO TEMPTABLE Values(104,'Naveen',NULL);

1 row created.

SQL> /*As you can see Primary Key Doesn't allow NULL Values*/
SQL> /*Unique key can have one or more NULL Values*/

SQL> INSERT INTO TEMPTABLE Values(104,'Naveen',NULL);
INSERT INTO TEMPTABLE Values(104,'Naveen',NULL)
*
ERROR at line 1:
ORA-00001: unique constraint (HR.PK) violated


SQL>/*You can insert NULL but can not Insert Duplicate Values*/

Another Example that you can check and see the difference.

Get involved and leave your Comments in the Box Below. The more people get involved, the more we all benefit.

So, leave your thoughts before you leave the page.