I admit I too am in the habit of always defining an auto_increment primary key, but
recently gathered my courage and omitted it from a match-up table joining a table of
users to a table of categories they were allowed to use - an auto-generated primary key
would have been completely redundant. It felt quite daring though! :o
Seriously though, in my experience at least, one is usually trying to represent something
'real world' in a database schema, and usually there just isn't something that clearly
fits the requirements for a primary key (well defined in the quote Rhino gave). So very
often one just has to create an 'artificial' pk and then it makes sense to use one which
will be as efficient as possible.
In an app I'm just finishing I split the core data into many tables, but still one of
those is 40 million rows and growing fast. So I shifted everything I could out into
related tables to try and reduce the column sizes. By my calculations every byte of
storage requirement I can knock off each row means 40 MB for that table's data file
alone, and probably nearly 1 GB (more with index files?) over the whole app. That's got
to have a significant effect on performance. For example, the date (year-month) is thus
shoe-horned into a tinyint. This means that in about 12 years the client will have to
think about bumping that column to smallint, but by then I suspect the performance
implications of that will be insignificant.
Heh, 12 years ago I woudn't have worried about shaving 40 MB off a 2 GB file, because 40
MB _was_ my hard disc! So presumably as the years go by increasing processor power and
storage speed & size will mean we will no longer have to compromise on purity of db
design to get acceptable performance.
OK, now I'm just rambling.