Why do we need a Unique key when we have a Primary key?

Question

I tried to Google this question but all I found were differences between primary and unique keys, why do we actually need a unique key when we have a primary key that can identify any single record, could someone provide an example to explain this or maybe provide a link that explains this.

Answer 1

Why do we actually need a unique key when we have a primary key?

Short answer -- You don't.

Long answer:

In MySQL, the PRIMARY KEY is a UNIQUE key is an INDEX.

There is only one PRIMARY KEY; its main function is to uniquely identify each row.

A UNIQUE key is allowed to contain a column that is NULLable.

UNIQUE is a uniqueness constraint and an Index.

Any flavor of an index may include multiple columns; the order of the columns in the definition matters. (The order of conditions in a WHERE clause does not matter.)

A FOREIGN KEY is a CONSTRAINT and it implicitly creates an INDEX if there is not already one that works for the constraint.

Each of these pairs is redundant; Drop the second:

PRIMARY KEY (a)
UNIQUE (a)

INDEX(c,d)
INDEX(c)

UNIQUE(e)
UNIQUE(e,f)

UNIQUE(h)
UNIQUE(g,h)  -- or consider making changing to INDEX(g,h)

If you build a table without a PRIMARY KEY but with a UNIQUE key, consider changing "unique" to "primary key".

It is very rare for a table to need 3 UNIQUE keys (including the PK); rethink the schema.

Some programmers always have an AUTO_INCREMENT (usually named id) as the PRIMARY KEY. But this is not always necessary, and it sometimes interferes with performance.

Caveat: Some of the above statements may not apply to RDBMSs other than MySQL and MariaDB.

Caveat: Index-prefixing, Partitioning, and a few other obscure things are not covered above.

Two common cases for a PK + a UNIQUE:

• Lookup table (for 'normalizing'): The table has an id (auto_increment, PK) and a string (Unique).
• Many-to-mapping table: Two columns, each being an id into some other table. The PK would be the pair of columns in some order; the UNIQUE would be the pair in the other order. (Technically, a plain INDEX suffices for that second index.)