Problem
When compared to auto incrementing numbers, is email address a poor contender for primary?
The email address in our online application must be unique within the system. As a result, I decided to use the email address as the primary key. My colleague, on the other hand, believes that string comparison will take longer than integer comparison.
Is there a good reason to avoid using email as the primary key?
PostgreSQL is the database we’re using.
Asked by robert
Solution #1
The comparison of strings takes longer than the comparison of integers. However, if you just utilize the e-mail address to obtain a person from the database, none of this matters. If you have sophisticated queries with several joins, it matters.
The e-mail address will be the foreign key to the users table if you store information about users in several tables. That suggests you’ve saved the e-mail address several times.
Answered by Sjoerd
Solution #2
I’ll also point out that using email as a unique field is a bad idea because people and even small organizations share email addresses. Email addresses, like phone numbers, can be reused. Jsmith@somecompany.com may easily be John Smith one year and Julia Smith the following year.
Another issue with emails is that they are always changing. If you use that as the key in a join, you’ll have to update the other tables as well, which can be a performance blow when an entire client company’s email addresses change (which I have seen happen.)
Answered by HLGEM
Solution #3
The primary key should be one-of-a-kind and unchanging.
As the seasons change, so do email addresses. It’s good for lookup as a secondary key, but not as a primary key.
Answered by Steven A. Lowe
Solution #4
The following are some of the drawbacks of using an email address as a primary key:
Benefits of using an email address as a main key include:
It’s hardly a slam-dunk either way, in my opinion. When a practical key is available, I prefer to utilize natural keys because they’re just easier to deal with, and the downsides don’t really matter in most circumstances.
Answered by Jay
Solution #5
No one seems to have brought up the issue of email addresses being considered private. If the primary key is the email address, a profile page URL will most likely be something like…./Users/my@email.com. What if you don’t want the user’s email address to be revealed? To build URLs like…./Users/1, you’d have to find another means to identify the person, perhaps by a unique integer number. After all, you’d wind up with a unique integer value.
Answered by Simen Echholt
Post is based on https://stackoverflow.com/questions/3804108/use-email-address-as-primary-key