Difference between Primary key and Unique key; MySQL

The differences between two very common MySQL keys which is the Primary key compared to the Unique key.

Primary key

Identifies a unique row in the table, as a default it is a clustered index. There can only be one primary key per table, it can not have a null value or a duplicate value. Aims to enforce data integrity.

Unique key

Multiple unique keys allowed per table as they help to eliminate duplicate rows. null value is allowed for a unique key. Default is a unique non clustered index, as the name suggests keeps values unique to the table.

Explanation

Look at the Primary key as a means to keep data integrity in your table whilst the Unique key is to prevent duplicate data, It is best to assign the Primary key to an identifier that is used to join other tables.

The Unique key also fits the criteria for this however as said above, use the Unique key to prevent duplicate rows.

In the example table above, the column uid is a Primary key because it won’t be null and it will be used to join from other tables. email and mobile columns are Unique keys due to them not being repeated in any other rows and they may have null values if the person doesn’t have an email address or a mobile phone.

In this example, Primary key works well with auto-increment unless you already have pre-defined id’s.