I know surrogate primary keys are generally recommended over natural primary keys but are the arguments in favor of the surrogate primary keys valid when it comes to usernames?
+3
A:
From a customer standpoint, you bet. I don't want my username to be DWong1145.
DWong
2010-01-27 08:43:33
+7
A:
NO, you should use username only as a unique key/constant. Username can be change, just and example: it may be a trademark and the owner requests you to drop it.
Sorin Sbarnea
2010-01-27 08:44:08
Sounds a little terse ;) You *are* right though. I hate systems on which you can't change your username - however, the OP said in the comments that he doesn't want it to be changed, so...
nbolton
2010-01-27 15:12:52
+6
A:
If you don't want to get duplicate usernames, create a UNIQUE constraint.
What if DWong1145 wants to change it's username? Will you to make all database relationships to be UPDATE CASCADE?
Rubens Farias
2010-01-27 08:44:40
My question is in the context of an authentication system that will not allow username changes. I should have mentioned that.
Emanuil
2010-01-27 09:03:10
@Emanuil - to quote Jeff Goldblum in Jurassic Park, "Life finds a way." if you need to change your usernames some day, you might be regretting this very moment right now...
Dan Beam
2010-01-27 09:23:19
"`Information` shouldn't be use as key; `data` can"; `data` doesn't need to be changed, as if always meant to be understood by a program. What if I call SO and to ask to change my user _id_? I can't do that, but I do can change my user _name_.
Rubens Farias
2010-01-27 09:30:39
+4
A:
It is a good candidate in some sense, however you have to consider if you really want to do it. For instance, you have a user with a certain user name, then the user gets "deleted" (or marked as deleted). So, there is actually no reason for not allowing to create another user with the same username, but it is already "taken", since it's a primary key.
naivists
2010-01-27 08:45:13
+1
A:
A big disadvantage of having a string as primary key, regardless of whether it's a username or something else, is that all foreign key columns referencing the table will also have to be strings, which is both slower and wastes more space.
Journey
2010-01-27 09:20:12
it doesn't have to be slower. good database engine could pre-hash any string values. on top of that, using natural keys will probably eliminate need for many joins in the application therefore making queries faster. I accept that natural keys will generally consume more space but honestly, space is cheap and plentiful.
lubos hasko
2010-07-28 08:40:55