Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 525
Steven Avatar asked Dec 01 '09 11:12

Steven


People also ask

What are three 3 rules that a database designer has to consider which choosing a primary key?

Uniqueness. Irreducibility (no subset of the key uniquely identifies a row in the table) Simplicity (so that relational representation & manipulation can be simpler) Stability (should not be altered frequently)

Can username be a primary key in mysql?

The username of the user table is the Primary Key.

What should be your primary key in the database?

A primary key, also called a primary keyword, is a key in a relational database that is unique for each record. It is a unique identifier, such as a driver license number, telephone number (including area code), or vehicle identification number (VIN). A relational database must always have one and only one primary key.

Should a primary key be unique?

Primary keys must contain UNIQUE values, and cannot contain NULL values. A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields).


2 Answers

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.

like image 97
Graviton Avatar answered Sep 20 '22 17:09

Graviton


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.

like image 30
Vinko Vrsalovic Avatar answered Sep 20 '22 17:09

Vinko Vrsalovic