Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

What are the major differences between Unique Key and Primary Key in MySQL?

share|improve this question
up vote 12 down vote accepted

A table can have at most one PRIMARY KEY constraint but it can have as many as you want UNIQUE KEY constraints.

Columns that are part of the PRIMARY KEY must be defined as NOT NULL. That is not required for columns that are part of UNIQUE KEY constraints. If the columns are not Nullable, then there is no difference between Unique and Primary Keys.

Another minor difference is that you can choose the name you want for a UNIQUE KEY constraint (and index). On the other hand, the PRIMARY KEY has the default name: PRIMARY.

share|improve this answer

One major difference

  • Primary key disallows nullable columns
  • Unique key allows nullable columns

Otherwise, there isn't much difference...

share|improve this answer

Something others have not pointed out:

  • If you don't explicitly declare a PK in InnoDB tables, it will create one under the covers for you. You cannot access, order, or filter by this implicit key. This has ramifications in terms of resources as each secondary index contains a copy pointer to the PK of the row.
share|improve this answer

protected by Paul White Mar 21 '15 at 9:08

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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