Tell me more ×
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. It's 100% free, no registration required.
up vote 5 down vote favorite

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

share|improve this question

3 Answers

up vote 8 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
up vote 2 down vote

Some things others have not pointed out:

  • //Redacted

  • 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
"Only a PK can be auto increment": This is not correct. You can try CREATE TABLE test (a INT NOT NULL PRIMARY KEY, b INT AUTO_INCREMENT, INDEX (b)) – ypercube Mar 26 '12 at 15:15
And the auto-increment does not even have to be Unique. INSERT INTO test VALUES (1,1), (2,1) ; will work fine. – ypercube Mar 26 '12 at 15:18
I stand corrected; updated to remove misinformation – atxdba Mar 26 '12 at 15:23
up vote 5 down vote

One major difference

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

Otherwise, there isn't much difference...

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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