Database design: Email as table's id

I'm working on a Java web application. For authentication I require the user to enter his email and password. Now, I'm using JPA 2, which maybe isn't so important.

If the email was the key of the Users table, it would simplify my life so much. I could do a simple:

User selected = em.find(User.class, userEmail);

See?, Also, every email address is unique, it has no spaces etc. Now, nobody does this, I'm guessing there's a reason. I have my doubts too, I mean, it's varchar etc. But do you think it's a good idea? If not, why?. It has to be a good reason, so that the trade off isn't worth it. Numeric keys are always best, yet here I find myse开发者_运维问答lf dealing with the user's email over and over, and searching them by email all the time, never really using the id except for join columns etc.

The biggest issue that comes to mind is that a user's email address will change over time. If you set up a system where the email address is the primary key then changing the email address later is a royal pain-- you'd have to propagate the change to all the child tables which would require that all the foreign key constraints be deferrable.

Apart from

• Waste of Space.
• Performance Issues in Joins.
• Waste of Space in indexes.

If the user is given the option to change his email id you will end up changing the Primary Key which will be a big effort/mess.

A table can have multiple keys. Make email a candidate key. I like to think of PKs like running for president of the USA. You have multiple candidates, but only 1 can be president.

What others have said is good. If the value changes you don't want to have to update 30 other tables. This favors a surrogate key (a key with no meaning other than identifying the row). Since surrogates have no meaning you don't have to worry about their value changing. Typically they're an auto-increment integer.

Back the the orignal point. Your code:

User selected = em.find(User.class, userEmail);

That code should be perfectly valid regardless of email being a primary or candidate key. If your framework REQUIRES searching on primary keys then you better run screaming becuase in the real world you search on much more than just the PK.