What are the major differences between
Unique Key and
Primary Key in MySQL?
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:
One major difference
- Primary key disallows nullable columns
- Unique key allows nullable columns
Otherwise, there isn't much difference...
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.
Most important difference is in their purpose.
- Primary Key: Purpose of Primary Key is to act as a “KEY”. A primary key is a key in a relational database used to identify records.
- Unique Index: Unique Index is an “INDEX” intended for performance. Optimizer knows that for a condition "where x =:x” there will be only one record as a result – so it can prepare a plan suitable for that.
- Unique Constraint: It is a “CONSTRAINT” which makes sure that there are no duplicate values in that column. It is a constraint for data integrity.
Apart from their purpose, following points are noteworthy.
- Unless otherwise specified PRIMARY KEY will try to create a CLUSTERED INDEX (This point is about SQL Server though, as mentioned in the comment)
- There can be only one PRIMARY KEY per table; but there can be many unique constraints and unique indexes
- PRIMARY KEY is always not null but columns with unique constraint can hold NULL values
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?