CODEWITHSUNDEEP
sqlprimary-key
drum
drum

Reputation: 5651

Username or Userid

I've seen many discussion whether is better to use userid or username as primary key for a table. userid would allow for the flexibility of later changing username if desired. Also is a way to implement security. However, username is also a unique identifier.

If I choose userid as my primary key, what is the best way to enforce username to take on a unique value?

If I choose username, what problems should I be aware?

Upvotes: 1

Views: 619

Answers (5)

Vikdor
Vikdor

Reputation: 24124

I would declare UserId as the PRIMARY KEY as there will be other tables referencing this user record through UserId and thereby will be useful to enforce any FOREIGN KEY constraints.

If username needs to be unique, then I would declare it as NON NULL column and define UNIQUE KEY constraint. The NON NULL property will prevent the single null value allowed by the UNIQUE KEY constraint in a column. So, this set up on UserName would be similar to that of a PRIMARY KEY.

Upvotes: 2

dotNET
dotNET

Reputation: 35400

For your first question, UNIQUE constraint is available in most modern RDBMS. You can implement it at application level too.

For your second question, I don't see any obvious problems. UserNames are generally known to the end-users. However, if you have large set of users and the max length of username field is large, indexing may not be as efficient as on int type userID fields.

Upvotes: 0

muratgu
muratgu

Reputation: 7311

what is the best way to enforce username to take on a unique value?

Create a unique index.

If I choose username, what problems should I be aware?

Will you allow a user to change their user name (as long as it stays unique) whenever they want? If yes, then use a user id that you generate; otherwise use a user name that they choose.

Upvotes: 0

John Woo
John Woo

Reputation: 263703

This is from my own point of view.

I rather choose UserID of data type int (or could be string) to be the primary key of the table since at all times this can't be change. And there is no problem on some foreign keys that are referencing on it since it is unchangeable.

The reason why I didn't choose Username is because at some point, although this is unique, can be change sometimes. If there are already foreign keys that are referencing to it, that username can't be change at all until those keys or records where dropped or deleted first.

Upvotes: 2

Pieter Geerkens
Pieter Geerkens

Reputation: 11883

Semantically, to my ear at least, userid sounds like an artificially created value, possibly an artificial primary key, while username sounds like a natural, user-friendly (component of) a natural primary key. To use either term in the opposite sense is likely to confuse programmers and users occasionally, and possibly create subtle bugs down the road.

Upvotes: 0

Related Questions