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



  • 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.

    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.)



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2