Re: IS it a good practice to use SERIAL as Primary Key?

From: John McCawley <nospam(at)hardgeus(dot)com>
To: Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)net>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: IS it a good practice to use SERIAL as Primary Key?
Date: 2006-11-27 18:53:04
Message-ID: 456B3410.4060601@hardgeus.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


>Yes, that's the point. They are legacy designs, and that portion of
>the design is wrong.
>
>
>
I'll weigh in my my .02 on this subject. After much pain and agony in
the real world, I have taken the stance that every table in my database
must have an arbitrary, numeric primary key (generally autogenerated).
I have found that this gets me into a lot of arguments with other
database guys, but never seems to cause any problems for me.
Conversely, I have seen innumerable problems in the real world caused by
the usage of actual data as primary keys.

Perhaps I am amazingly ignorant, but I have yet to find a case where my
approach causes any real problems. What does using "real" data as a
primary key buy you? The only real advantages I can see are that an
individual record's data will be somewhat more human-readable without
joining to other tables, and that your search queries can be simpler
because they don't have to join against other tables.

On the (many) occasions that I have worked on databases with "real" data
as primary keys, I just saw so many problems arise. In the real world,
data changes, even supposedly unchangeable data. When using arbitrary
primary keys, all you have to do is change the data in the one table
where it lives. If you are using real data as your keys, you have write
complex queries or code to "fix" your data when the supposedly
unchangeable data changes.

Anyway, I'm sure this is a huge argument, but that's my 0.2

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Brandon Aiken 2006-11-27 19:21:07 Re: IS it a good practice to use SERIAL as Primary Key?
Previous Message Tony Caduto 2006-11-27 18:41:45 Re: PostgreSQL doesn't accept connections when Windows