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.
Why do we actually need a unique key when we have a primary key?
Short answer -- You don't.
In MySQL, the
PRIMARY KEY is a
UNIQUE key is an
There is only one
PRIMARY KEY; its main function is to uniquely identify each row.
UNIQUE key is allowed to contain a column that is
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.)
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
idinto some other table. The PK would be the pair of columns in some order; the
UNIQUEwould be the pair in the other order. (Technically, a plain
INDEXsuffices for that second index.)