Primary key question

Question

Is there a benefit to having a single column primary key vs a composite primary key?

I have a table that consists of two id columns which together make up the primary key.

Are there any disadvantages to this? Is there a compelling reason for me to throw in a third column that would be unique on it's own?

Answer 1

Database Normalization nuts will tell you one thing.

I'm just going to offer my own opinion of what i've learned over the years..I stick an AutoIncrementing ID field to Every ($&(@#$)# one of my tables. It makes life a million times easier in the long run to be able to single out with impunity a single row.

This is from a "down in the trenches" developer.

Answer 2

Single column keys are simple to write, simple to maintain, and simple to understand.

If you're going to have a huge number of rows - billions? - maybe saving a byte here and there will help.

But if you're not looking at extreme cases, optimizing for "simple" is often the best way to go.