Should all database tables have a primary key?

Question

Is it good practice to give every database table a primary key? It seems to me that if the primary key is not explicitly needed, then it would just be extra clutter in my database.

Answer 1

When you probably WOULD:

In an OLTP database you'd almost always (in my case always) have a primary key of some sort. Sometimes Guid, sometimes autonumber/identity fields, sometimes set by the application or the customer. Sometimes even a combination of more than one field. This is because you'll typically want to uniquely identify any given row from the table.

Also, a primary key is a constraint used by the query optimiser that should improve performance for lookups and joins.

When you probably WOULDN'T:

The only time you wouldn't have a primary key is in a "reporting" table, probably in a denormalised data warehouse.

Answer 2

Yes, it is good practise to have a primary key on every table.

But, NOT every table should have a single auto number id column. I felt the need to spell that out, because for some reason lots of people tend to throw in an extra ID in all tables even though a perfectly good candidate already exist. For example, a many-to-many table representing Users <-> Groups should use {user_id, group_id}.

Apart from stopping duplicates at the door, a primary key constraint also carries information which is used by the optimizer when generating execution plans.

This is why I always, or at least with very few exceptions, have a primary key on all tables I create. In fact, I even create primary keys on reporting tables where most of the columns are part of the primary key. Because during development, I will get at least one unique constraint violation because I did something wrong. With shitloads of data and no constraint in place I wouldn't have spotted the error.