Re: integer id columns for all tables

garhone wrote:
>
> Someone with greater expertise than I recently told me that it is best
> to have an integer primary-key for all tables in a database, even if
> the table already has some non-integer primary key, or some sort of
> composite primary key. If there is already a non-integer key, then
> create a new sequence column and set that as the primary key.
>
> Does anyone have an opinion on this and why?

I would suspect that pretty much everyone will have an opinion. Mine is that the guy is a dipshit. He's overgeneralized the issue.

Consider a simple example: a join table. Here's a table with two columns: one that's the primary key of table A, and one that's the primary key of table B. Of course we have the pair of columns as a key. What are the queries you're going to be running on this table?

select all A values where B.id = ?
select all B values where A.id = ?
delete all values where A.id = ?
insert (?,?)

And various joins with tables A and B. You can make up your own, too.

Now, what does adding an extraneous integer primary key to this table do? It makes all the queries and updates more complicated. Uses up more space. Doesn't add a damn bit of value.

I've run in to this issue a lot in the field, where a table has a bogus additional integer field. It's really annoying, and sometimes a source of significant difficulty. Sometimes they even skip the uniqueness constraint on the domain-specific attributes because they already have one in the form of the bogus integer, which causes additional problems with duplicate data.

Just occurred to me: another way to think about the problem is that the default hypothesis about adding a new column is that you don't add it. To add any column to any table, you have to have a valid reason, or you don't put it in.

What's the valid reason for the extraneous unique integer?

Marshall Received on Mon Aug 28 2006 - 18:55:45 CEST