6

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?

4 Answers 4

11

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.

3
  • 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, 2011 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, 2011 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, 2011 at 14:23
6

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.

1
  • 2
    Could not should, as sometimes you want to create a new ID at this point.
    – Phil Lello
    May 6, 2011 at 21:23
3

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.

1

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".

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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