2

I have a table Users.

And I have a auto increasing long id as primary. Now I need the username also to be unique. What would be the best practice?

Should I only keep the username and remove numeric id? Should I somehow make them both unique? What do you recommend?

Maybe should also mention that using hibernate.

Jaanus
  • 16,161
  • 49
  • 147
  • 202
  • How about making username also as a primary key? – Pradeep Simha Dec 10 '12 at 15:26
  • You can only have one primary key which is a combination of 1 or more columns. If username/id was the primary key then you could have keys like (1,Dave) and (2,Dave) which would allow non-unique usernames. – Dave Dec 10 '12 at 15:29
  • I don't think it makes any difference that you're using Hibernate; data integrity should be enforced in the database. However, you might like to use the [`unique`](http://docs.oracle.com/javaee/6/api/javax/persistence/Column.html#unique%28%29) attribute on the `@Column` annotation on the username field; i don't believe it will have any functional effect, but it serves as documentation. – Tom Anderson Dec 10 '12 at 15:33

4 Answers4

5

Typically you would add a "unique constraint" on the username. Having the auto-increment id is not strictly needed but it is a common practice to not use a "natural key". See this related question: Native primary key or auto generated one? .

Community
  • 1
  • 1
Dave
  • 13,518
  • 7
  • 42
  • 51
2

To keep username unique you just need to:

ALTER TABLE Users 
ADD CONSTRAINT constraint_name UNIQUE (username);

No need to change the primary key. It is useful, to have an id primary key. And it allow you to change user names, without the need of UPDATE on linked tables.

UPD: The only case, when you wont want to keep two PK (one real, another 'semi' PK) is when the table get tons of UPDATE/INSERT/DELETE per second. In this case the overhead of 2 indexes vs 1 index can be noticeable.

Ihor Romanchenko
  • 26,995
  • 8
  • 48
  • 44
1

It depends how your application is used. If the username can ever change I would recommend using an ID as the primary key - you can always put some checks in your code to ensure that the username is unique when it is created.

It is common practice to use an ID (primary key) when accessing data from a database as you could introduce unintended behaviour which is hard to debug if you were to use anything else.

maloney
  • 1,633
  • 3
  • 26
  • 49
  • how about logging in? can't use ID for that, can I? – Jaanus Dec 10 '12 at 15:31
  • 1
    When logging in just search for the unique username and, as you are using Hibernate, return and store in the User object. This will bring with it the ID for the user which you can then use for any other tasks that require that User later on. – maloney Dec 10 '12 at 15:45
1

Can the username ever change? If not, you could use it as the primary key. It would be a natural key, and as such, in my opinion, better than a surrogate key, such as you are currently using.

If the username could ever change, then it is not suitable for use as a primary key.

Since you can't know all the scenarios which could arise in the future, i suspect you can't rule out the possibility that the username might need to change. Therefore, the surrogate key is the future-proof option.

It's perfectly fine to have a unique constraint on the username even though it's not the primary key. You very likely also want a not null constraint on it.

Tom Anderson
  • 46,189
  • 17
  • 92
  • 133