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

mysql database-design primary-key unique-key
Steven picture Steven · Dec 1, 2009 · Viewed 24.2k times · Source

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?

Answer

Graviton picture Graviton · Dec 1, 2009

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.

Related questions

Remove Primary Key in MySQL

I have the following table schema which maps user_customers to permissions on a live MySQL database: mysql> describe user_customer_permission; +------------------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------+---------+------+-----+---------+…

Read More
Auto Generate Database Diagram MySQL

I'm tired of opening Dia and creating a database diagram at the beginning of every project. Is there a tool out there that will let me select specific tables and then create a database diagram for me based on a …

Read More
What is the ideal data type to use when storing latitude / longitude in a MySQL database?

Bearing in mind that I'll be performing calculations on lat / long pairs, what datatype is best suited for use with a MySQL database?

Read More