Re: are primary keys always 'needed'

Serge Fonville <serge(dot)fonville(at)gmail(dot)com> writes:
> I was wondering if primary keys are always needed.

As you say, there are different schools of thought on that. To my mind,
the principal argument for "always have a primary key" is that it
guarantees that no two rows in your table are exactly alike. The reason
why that's a good idea is that SQL only allows you to reference rows by
content. If you have two identical rows, and you wish to get rid of
just one of them, how you gonna do that? If there is no difference you
can use in DELETE WHERE ..., you're stuck.

Now there is an "out" available in Postgres, because in PG there is
always a hidden system column called "ctid" which is the row's physical
location (to oversimplify a bit). So when your back is to the wall you
can reference a specific row by ctid, even if you neglected to provide
a primary key. But not all DBMSes have an equivalent concept.

Anyway, I'm not in agreement with the school that says you must always
have an explicitly named primary key. Sometimes there are other
constraints that have the same effect of ensuring no duplicates, and
sometimes you just plain don't care if there are duplicates. But it's
always a good idea to think about that carefully. I believe that in
*most* cases you want a primary key.

regards, tom lane