views:

177

answers:

3

I was told by a friend:

What unique key do you use? I hope you are not saving the entire user name --- this will use up too much table space! Assign an unique userID to each (unique) userNAME and save this userID (should be INTEGER UNSIGNED auto_increment or BIGINT UNSIGNED auto_increment). Don't forget to create a reference

FOREIGN KEY (userID) REFERENCES usertable (userID) in all tables using the userID.

What do you think of my friend's comment?

+9  A: 

I think he is right ( for the wrong reason) because primary key cannot change, but username can change. So you should use userid because it wouldn't change.

Ngu Soon Hui
The primary key cannot change. Or it isn't "primary". The key should be system-assigned, to guarantee that it can never change. Other fields (like name) can have unique indexes, but cannot be a primary key -- by definition.
S.Lott
The application specifies some field as primary key and thus non changing. The application can later change, making a field originally defined as unique and non changing, to allow duplicates or to change, which is the point here.
Vinko Vrsalovic
+3  A: 

He is right for the wrong reasons. The table space is secondary to the fact that your app might later mandate that usernames can be changed or even stop being unique (you could envision an application where unique usernames are not required, like Stack Overflow) and thus your app would need major refactoring and data migration instead of a light change in the other (integer PK) case.

Vinko Vrsalovic
A: 

Your friend is right, though his reason is "just a scratch on the top of an iceberg". The purpose of a primary key is to uniquely identify a row in table, not necessarily the "logical entity" -- your user. If you happen to have one user entry per row, then the user will be identified too. If you later decide that you want to keep some historical user records in the table, you can use a business key, like "FirstName & LastName & Pin" to identify all rows specific to a user, but each row will have its own primary key. The auto-increment integer is the best option, whenever possible.

Damir Sudarevic