What is the difference between "Primary Key" and "Unique Key"?

I've had a chance to with a Postgres database and came across a table with two primary keys. This intrigue me to lookup a definition of primary and unique key. I always thought there will only be one primary key in a table, and the values have to be unique. But, what I saw in the PostgreSQL v8.1.23 was something otherwise. It allowed duplicate entries, and also allowed multiple primary keys in a table. Is this possible? Yes, it's called composite Primary Keys. Composite primary keys (multiple primary keys) make up a uniqueness in a table row -- which means composite primary keys working together to provide a uniqueness.

Here is the definition of primary key and unique key constraints in a SQL table.

Primary Key
Primary Key is an attribute or a set of attributes in a table which uniquely identifies a record (row) in a table and no two records (rows) in the table can have the same values for all the columns comprising the primary key.

A primary key can consist of one or more columns of a table. When two or more columns are used as a primary key, they are called a composite key. Each single column's data can be duplicated but the combination values of these columns cannot be duplicated.

Unique Key
A unique key is a key which stores unique values (no duplicates) for that particular column, and it accepts null value and multiple null values do not violate unique constraint.

Difference between Primary Key and Unique Key
1. A Unique key can store a null value and primary key cannot store any null value.
2. A primary key can be references to another table as a Foreign Key.
3. A table can have only one primary key, but have multiple unique keys.
4. Primary is not always a single-column key, and may consist of multiple columns to make a composite key.



Add new comment

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.