Difference between Primary Key and Unique Key in Oracle

Key difference: Primary key and unique key are entity integrity constraints. The main difference between the two keys is that there can be multiple unique key columns with null values, whereas that there can only be one primary key column with no null values on a table.

In Oracle, primary key and unique key are two important concepts in relational database, and are used to uniquely identify a row in a table. The primary and unique key constraints on a table each ensure uniqueness in the data for the column. There are considerable differences in each type of constraint; this article helps to differentiate between the two.

A unique key is a key that uniquely defines the characteristics of each row. Each unique key is composed from one or more data attributes of that data entity. It is often referred to as the candidate key for that data entity. The unique key, of one data entity, copies the data attributes to another data entity to which it relates. This inheritance of the unique key is referred to as a foreign key and is used to provide data access paths between the two data entities.

The unique keys become unique indexes associated with their assigned database tables, and the entity relationships become foreign key constraints. It can also be defined as:

ALTER TABLE <table identifier>

ADD [CONSTRAINT <constraint identifier>]

UNIQUE (<column expression>, {<column expression>}...)

A primary key is a key that uniquely defines the characteristics of each row. The primary key consists of characteristics that cannot collectively be duplicated by any other row. A primary key is a special case of unique key. Primary keys may consist of a single attribute or multiple attributes in combination.

A primary key is a unique identifier for a database record. When a table is created, one of the fields is typically assigned as the primary key. While the primary key is often a number, it may also be a text field or other data type. From the set of candidate keys, a single unique key is selected and declared as the primary key for that data entity. It can also be defined by:

ALTER TABLE <table identifier>

ADD [CONSTRAINT <constraint identifier>]

PRIMARY KEY (<column expression>, {<column expression>}...)

Comparison between Primary Key and Unique Key:

	Primary Key	Unique Key
Definition	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.
Null	It cannot be null.	It can be null.
Index	It creates clustered index.	It creates non-clustered index.
Key rows of a table	It allows each row in a table to be uniquely identified and ensures that no duplicate rows exist and no null values are entered.	It is used to prevent the duplication of key values within the rows of a table and allow null values.
Identification	It is a unique key identifier.	It cannot be the candidate key.
Key column	A table can have only one primary key column.	A table can have more then one unique key column.
Foreign key	It cannot be referred to another table as a Foreign Key.	It can be referred to another table as a Foreign Key.
Uniqueness	They are unique by definition.	It is represented by unique constraint.