CODEWITHSUNDEEP
mysqlsql
Tatarin
Tatarin

Reputation: 1298

In MySQL does a UNIQUE varchar have to be a PRIMARY KEY as well?

I have 2 unique values under 1 table in MySQL. 

|id (PK)|varchar(255) UNIQUE|
-----------------------------
|   1   |      1234abcde    |
|   2   |      5678fghij    |
|   3   |      9012klmno    |

Do I have to set UNIQUE varchar as a PK or I can still use auto_incremented id (int)? Is this a good or bad practice?

Upvotes: 2

Views: 1037

Answers (4)

Matthew
Matthew

Reputation: 9949

No it does not need to be a primary key.

Additionally: you cannot have more than one 'primary key' (different than compound primary keys), and it frequently makes sense to have an auto-increment id field for convention alone and for some frameworks (e.g. ORM's like Hibernate and Entity Framework).

To answer your second question, there are many business cases where you many enforce a unique constraint on multiple columns without making them your primary key - for example they may have to be unique, but you may need to be able to edit/change them:

i.e.

  1. Email addresses for usernames - forced unique, but users will likely need to update them
  2. Password salts (if your generation is sound this is unlikely to require enforcement)
  3. random strings used to generate one-time or time sensitive links (think bit.ly)

So, the point being this is done all the time.

Upvotes: 1

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52117

One table can have multiple keys. One of them is "primary" (PRIMARY KEY) and the rest are "alternate" (UNIQUE constraint).

But beware that under InnoDB primary key also acts as a clustered index, so choosing one key or another to be primary can have profound performance implications.

Upvotes: 2

MD Sayem Ahmed
MD Sayem Ahmed

Reputation: 29166

No, you don't have to. If you want to ensure uniqueness in your varchar column, set a unique constraint, but that doesn't mean you need to make it a PK. You can still use a numerical auto incremented id as your primary key.

This types of numerical primary keys which have no real world value are known as surrogate keys, and their use is recommended, specially when there are much more searching in the table than insert. They can be really helpful to speed up the query performance, specially during join and searching.

Upvotes: 1

Refugnic Eternium
Refugnic Eternium

Reputation: 4291

No, it doesn't have to be a primary. Primaries are unique, but not every unique is a primary.

Upvotes: 6

Related Questions