1

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.

1
  • Also worth mentioning (from here): If a table has no PRIMARY KEY or suitable UNIQUE index, InnoDB generates a hidden clustered index named GEN_CLUST_INDEX on a synthetic column that contains row ID values. The rows are ordered by the row ID that InnoDB assigns. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in order of insertion. Jan 22 at 9:14

1 Answer 1

2

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.)
2
  • As every table has to have a primary key, what is the purpose of unique key at all, it could just be similar to any other column Jan 22 at 2:52
  • 1
    @SwayamShah - I added the two main examples of needing two Unique indexes.
    – Rick James
    Jan 22 at 6:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.