Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In a table, such as a user table, where there will only be 1 row with a for any particular user, why have an ID field, let alone as the primary key?

share|improve this question
add comment

4 Answers

up vote 11 down vote accepted

I'd answer with a question: Why do you need a table for a single row? Regarding the design of the table, any table should have a primary key. I won't discuss that as it's the baseline of any design book. Regarding the way of designing that primary key, there are two schools of thought:

  • one that makes the PK as a separate column, usually self generated, like a GUID or and auto increment INT (in your case a separate uniqueidentifier column);
  • one that makes the PK as a column (or set of columns) internal to the table (in your case would be a username or email or SSN, whatever makes that user unique) that uniquely identify a record.

To what line you adhere, it's only a matter of taste.

share|improve this answer
3  
I try to stay away from compound PK's as any other tables that refer to this table using a foreign key will also have to have those compound columns. I create a synthetic PK, then use a unique constraint on the compound columns. –  datagod May 5 '11 at 13:04
1  
@datagod: I agree with you, I do the same in my current environment. But it's still a matter of –  Marian May 5 '11 at 13:46
1  
LOL, I forgot to finish the phrase. What I wanted to say is that I think that it is a matter of personal choice. –  Marian May 5 '11 at 14:23
add comment

You could use the ID instead of the username in references, so that you can rename users and retain a proper audit trail.

If user jane.doe marries john.smith, she might want to change username to jane.smith

Generally, people are more often concerned with name changes in case of divorce, but it's also useful to correct typos, or switch john to john.smith when a company grows.

share|improve this answer
2  
Could not should, as sometimes you want to create a new ID at this point. –  Phil Lello May 6 '11 at 21:23
add comment

Not sure what you mean by a "user" table in this context but ...

You are right that a table whose constraints limit it to a single row does not actually need any key attributes. In relational database terms that would be a relation with an "empty" key - a key which consists of no attributes at all. Unfortunately no SQL DBMS supports such a thing. One possible workaround is to create some arbitrary "key" attribute, put a uniqueness constraint on it and also apply a CHECK constraint to limit the "key" to a single value. So that's a reason why you may need to create a key attribute (strictly it's a superkey, not a key) even though you don't appear to need it.

share|improve this answer
add comment

Are you referring to a table with a single row? Or a table with multiple rows, each uniquely identified by some string? Assuming the latter...

ID INT UNSIGNED AUTO_INCREMENT, Name VARCHAR(2000) -- There you need the ID, because Name is too big to be an index.

ID INT UNSIGNED AUTO_INCREMENT, Name VARCHAR(10) -- There is very little advantage for having the ID if Name is unique. It can be used instead.

ID INT UNSIGNED AUTO_INCREMENT, GUID VARCHAR(36) -- If this is a standard UUID/GUID, then first of all, it should be declared BINARY(36) to avoid collation overhead and *3 for utf8. Anyway, it is borderline whether the GUID should, itself, be the PRIMARY KEY (and get rid of ID).

See also, discussions on "Normalization".

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.