Should I use a composite primary key here?

I have a table with only two columns: a primary key and a unique column. the primary key is always paired with a value from the unique column. The pairings are always unique (by design in my app). I do not do searches on this table except via one column or the other, and never both at the same time, and then only to get the content of the other column. (example: I will search by id to get a user name, and search by user name to get an id, but I never have both in a single where clause). So my question is whether I should I make these two rows into a composite primary key and what benefit might that bring if I do?

no, don’t do that

if you make the two columns a primary key, here is what you might get

4102 tom
4103 dick
4104 harry
4104 hairy
5812 tom
6937 tom

I don’t quite understand your post about what I might get. Do you mean an id might have more than one name associated with it? I don’t want that, but I don’t understand how that follows from setting both columns to be the primary key.

you can either believe me, or do some testing :wink:

I didn’t mean to imply that you were wrong, only that I did not understand. (My apologies if it came across that way.)

4102 tom
4103 dick
4104 harry
4104 hairy
5812 tom
6937 tom

All of these are unique when taken together, they do not need to be individually unique.

4104 harry and 4104 hairy are both unique composite keys, because the name is different therefore are different when taken together.

4102 tom, 5812 tom and 6937 tom are different because the numerical id is different and therefore are different when taken together.

If you want the names to be unique, set the column index definition to unique. (like it sounds like you’ve already done)

Thanks. I understand the outcome now. (I was considering that each column was marked unique before, but now I see that wasn’t being considered in the replies, so there was confusion on my part.)

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.