CODEWITHSUNDEEP
mysqldatabase-designprimary-keyunique-key
Steven
Steven

Reputation: 25284

Is it bad to use user name as primary key in database design?

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.

Is the above statement correct? Why or why not?

Upvotes: 32

Views: 35037

Answers (2)

Graviton
Graviton

Reputation: 83254

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.

Upvotes: 59

Vinko Vrsalovic
Vinko Vrsalovic

Reputation: 340191

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.

Upvotes: 21

Related Questions