Primary key = email address

[feders]

I have created a MySQL file whose primary key is an email address (since it is unique). However, when I try to select a matching record it does not like the @ sign in it. I am not sure how to tackle this. Do I modify the code to use (at) in place of the @ sign? Do I make a 2 part primary key, where username is part one and URL is part 2 and I never have to enter the @ sign at all? Or, do I rewrite my code (somehow) to get around the @ sign problem?
Snippets of code:
===================== the SQL table
CREATE TABLE userTable
( emailAddress varchar(50) primary key,
personName varchar(30) not null,
passWord varchar(30) not null,
cdate DATETIME);
================
it pulls this emailID from post method from previous form
$emailid= trim($_POST['emailID']);

$sql = "SELECT * FROM $dbtableName where emailAddress = $emailid";
It complains about this SQL statement
I would appreciate any help on this!

[wysiwyg]

Your SQL should be:
$sql = "SELECT * FROM $dbtableName where emailAddress = '". mysql_real_escape_string($emailid) ."'";

Or even better, use PDO and prepared statements.

[disgracian]

What is the specific error message?

This example is atrociously vulnerable to SQL injection also, but we'll get to that in good time.

Cheers,
D.

[feders]

Your SQL should be:
$sql = "SELECT * FROM $dbtableName where emailAddress = '". mysql_real_escape_string($emailid) ."'";

Or even better, use PDO and prepared statements.

Thank you so much! That's exactly what I needed.
Sandy

[dbevfat]

I would reconsider using e-mail for primary key. E-mail is mutable data and this can cause problems later on.

[kyberfabrikken]

I would reconsider using e-mail for primary key. E-mail is mutable data and this can cause problems later on.

My thoughts exactly, upon seeing this topic.

[r937]

what "problems" can be caused by a mutable PK that cannot be solved with an UPDATE statement?

this eagerness to use auto_increments everywhere causes as many if not more problems

what is one of the most frequent questions posted on database forums?

"HALP!!!1! i need to remove all but one of the duplicates in my table!!!"

[Selkirk]

I'd agree. email as primary key is problematic on many levels.

• People change their emails
• You shouldn't expose their emails, such as in a profile url.
• Emails are hard to validate
• You have to validate for security purposes
• Character encoding issues on text field primary keys can be tricky
• Large primary keys (>4 bytes) result in slower joins

[r937]

People change their emails

that's not problematic, that is a reality that must be acknowledged

luckily, SQL acknowledges that data sometimes changes, and provides the UPDATE statement for this purpose

and changing emails does not require the use of an autonumber




You shouldn't expose their emails, such as in a profile url.

of course you shouldn't

i agree, don't show emails

but not showing them does not require the use of an autonumber




Emails are hard to validate
You have to validate for security purposes
Character encoding issues on text field primary keys can be tricky

use an opt-in process which requires an email reply

and validating emails does not require the use of an autonumber



Large primary keys (>4 bytes) result in slower joins

myth

besides, even if you use an autonumber, you ~still~ have to have an index on email

[dbevfat]

It's not "eagerness to use auto incremental everywhere", it's "using the right tool for the job". You don't even have to use an autoincremental value, you can generate an arbitrary value, as long as it isn't mutable, and is fixed-length. Natural keys can be useful, but not in all cases. In the end it depends on your application, but it's usually not good.

First, you don't want to update your primary key - it's supposed to be permanent in such a way that the same value always refers to the same entity. Also, when a user modifies his/her e-mail, you have to update all the references to the user in other tables, which means a lot of updates and table locks. Think updating a table that records user events throughout account life-time (purchases, messages, etc).

To stay with references (and as Selkirk already pointed out), you need to use e-mail when (if) you need to reference a user from elsewhere. Sending a message to a user exposes the e-mail. Viewing a profile also. These issues can be overcome with generating a unique reference value for each user and send messages to that value rather than to an e-mail address. But then why not just use that as your primary key?

Another drawback is that if your application becomes large, data size may also be a problem - every reference will be storing significantly more data than needed, compared to an INT. Indices are less effective with human values, so their sizes must be increased, resulting in larger (and slower) indices, compared to INT.

Also, joins on larger fields in fact are slower, it's not a myth. At least on MySQL. VARCHARs are the slowest to join, and that's what you're using for the e-mail field. See this, this, and other posts on their blog.

Last but not least, you definitely don't want to change the data type of your primary key. With mutable data in general, this can happen - like changing varchar[50] to varchar[60] for a few users with really long e-mails. Not only that means altering the structure of your userTable, it also means altering any table that refers to it.

There's not much more to be said. If you think all these possible issues won't happen in your application, then I guess you've selected the correct primary key.

regards

[TomB]

iirc you should be able to set an "ON UPDATE CASCADE" to automatically update all references to the PK providing you've set up your foreign keys correctly, (though i've never tried it myself) but I still wouldn't use mutable data for the primary key.

using username for primary key may be worthwhile however, depending on the situation. For example, if most queries on the field are coming from exposed data (e.g. GET), it's nicer to show the username than an ID number.

[felgall]

An email address is not something that would change frequently enough to cause any problems. It is probably a much better primary key than most alternative values and definitely better than an arbitrary one.

[Selkirk]

r937,

Regarding the updates ... You think all your references are going to just be in your DB? Integrate with external systems much?

You need an opaque identifier for members for the times where you are passing that ID outside of your control (The profile url problem.) Why not just use that as your primary key? (as dbevfat wisely points out.)

If you don't validate the email before creating the record, do you really want a bunch of XSS stuff in your primary key?

Regarding the join performance. That's a fact. I've personally benchmarked it out on real data in a production system.

Long primary keys also take up more space in your indexes. That can cause you to have to bump up buffer sizes to accommodate compound indexes.

I have direct experience with using email as primary key on a members table. I inherited a system that does this. I would definitely never do it on a new system.

[McGruff]

Using a single field to store two types of information - a record identifier and a record attribute (email address) - surely breaks one of the normal forms? (Don't ask me which one exactly).

[r937]

Using a single field to store two types of information - a record identifier and a record attribute (email address) - surely breaks one of the normal forms? (Don't ask me which one exactly).

you're thinking of first normal form, in which every column must hold atomic values, but that's not the case here

using email as the primary key does not violate any normal form

see how pervasive this idea of a "record identifier" (a.k.a. numeric surrogate key) is -- people think you need one or your table isn't "normalized"

if anyone is interested, here is a nice 3-part article which refutes the idea that mutability automatically rules out the possibility of being a PK -- Primary Keyvil, Part I

[r937]

Long primary keys also take up more space in your indexes. That can cause you to have to bump up buffer sizes to accommodate compound indexes.

do not be fooled into thinking you can just declare a numeric surrogate key to avoid this issue

you must ~also~ declare a unique index on the "real" key, or else risk duplicate data

what is one of the most frequent questions posted on database forums?

"HALP!!!1! i need to remove all but one of the duplicates in my table!!!"

[Ren]

you must ~also~ declare a unique index on the "real" key, or else risk duplicate data

But if have email address as the primary key, you must also have indexes on email address columns in other tables.

Thereby exploding you index size, relative to the number of related tables. And also increasing the cost of updating the email address, even if the RDBMS & table type supports cascading updates.

If people are having trouble with duplicate rows, that's because they haven't declared unique constraints, not because they haven't declared it as a primary key.

[r937]

But if have email address as the primary key, you must also have indexes on email address columns in other tables.

if you would kindly do a quick review of this thread, you might notice that there aren't any other tables

If people are having trouble with duplicate rows, that's because they haven't declared unique constraints, not because they haven't declared it as a primary key.

that's what i said, a couple of times

you need to have a unique constraint in addition to the surrogate key, thus rendering arguments about the length of the column being indexed irrelevant

[felgall]

But if have email address as the primary key, you must also have indexes on email address columns in other tables.

Why must you have them? Just because it is a primary key doesn't mean that the foreing keys pointing to it in the other tables need to have indexes.

That there are fields in other tables that reference it is a good reason for making it the primary key.

[dbevfat]

Why must you have them? Just because it is a primary key doesn't mean that the foreing keys pointing to it in the other tables need to have indexes.

Because you want to query other tables by this value, obviously. "messages" table has to be queried for incoming messages with "to" field, your outbox must be queried with "from" etc. Also, you can't make it a true foreign key without a constraint, which requires an index.

That there are fields in other tables that reference it is a good reason for making it the primary key.

This logic is circular and doesn't favor e-mails for PK. Same can be said for any primary key, because by definition you refer records by their primary key.

[r937]

Because you want to query other tables by this value, obviously.

which other tables?

have another look, there is only one table mentioned in this thread

[dbevfat]

which other tables?

have another look, there is only one table mentioned in this thread

feders didn't say anything about it being the only table either, so you can't really say. If you'd have to guess, you'd consider the probability: how many projects are ever done with a single table vs. how many are there with many tables?

Besides, the conversation has long ago departed discussing this particular case, we're talking about e-mail as PK in general. I think this is fairly obvious. And after all, I've clearly said that in the end it depends. There definitely are cases where e-mail makes a good PK, but with lack of more information, I can't say that for this case. And since feders is asking a pretty basic question (and having unsafe code, and using a varchar(30) for password), I felt safe to assume that he isn't really think about pros and cons of using an e-mail for PK -- he probably even isn't (wasn't?) aware that it can be a hard decision that will affect much of the code developed further on.

[felgall]

This logic is circular and doesn't favor e-mails for PK. Same can be said for any primary key, because by definition you refer records by their primary key.

You are looking at the table accesses backwards. You start from the tables with the foreign key and from there do the lookup of the matching record by its primary key.

If you are going the other way then the primary key of the first table would be a part of the primary key of the other tables and not a foreign key.

[dbevfat]

You are looking at the table accesses backwards. You start from the tables with the foreign key and from there do the lookup of the matching record by its primary key.

If you are going the other way then the primary key of the first table would be a part of the primary key of the other tables and not a foreign key.

I think you've misunderstood me, because I'm pretty confident I know what a foreign key is and how it's used . I was referring to your comment in which you said (as I understand) that the fact you're referencing a record from other tables, makes that reference column a candidate for a primary key in the first table. Well, to have a field referencing another table, you have to ... well, be referencing it first, and you do that by having a primary key in the first table already. (While you don't necessarily have to reference a record in another table via primary key, this is usually the case.)

[felgall]

Well, to have a field referencing another table, you have to ... well, be referencing it first, and you do that by having a primary key in the first table already. (While you don't necessarily have to reference a record in another table via primary key, this is usually the case.)

So you agree with me then.