Deciding upon a Primary Key

[carpeliam]

Most often, people use ID, an auto-incrementing number, as a PK. I currently have a table that includes Name and Email, among other things. Email must be unique. I'm using ID as the primary key, which is also a foreign key in many other tables. When somebody logs in, I need to run the following:

SELECT ID FROM Table1 WHERE email = "you@dot.com";
SELECT * FROM Table2 WHERE ID = ...;

Is there any reason not to just have email as the Primary Key, and just have the following:

SELECT * FROM Table2 WHERE email = "you@dot.com";

or am I forgetting something? Liam Morley
imotic@hotmail.com
"light the deep, and bring silence to the world.
light the world, and bring depth to the silence."

 

[rycamor]

I guess there is nothing directly wrong with it, if it accomplishes what you want. However, I think its much more efficient for the database to index integers, rather than text strings. Also, you are not really enforcing referential integrity, which could get you into trouble if your application expands later.

I suppose your first statement could be a JOIN, so you just run one SQL statement, but I don't think that really matters in this case.

 

[steve1]

Basically, there are four reasons for not using the Email address:

1. Inefficient(numbers take less space, and are faster to check.).
2. Insecure. (do you REALLY want your email address all over the place?)
3. It could change! Ideally, keys should NEVER change!
4. Not unique (A person could have several Email addresses, and what then?)