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

On Tue, 2006-11-28 at 09:29 +0900, Michael Glaesemann wrote:
> On Nov 28, 2006, at 9:02 , Joshua D. Drake wrote:
>
> > On Mon, 2006-11-27 at 17:31 -0600, John McCawley wrote:
> >> I promise I'm not trying to be a pain in the butt ;) Do you then use
> >> your serial id as your foreign key in other tables, or the
> >> firstname/lastname primary key?
> >
> > Now that is a good question. I would use the id, but that is not
> > technically proper :).
>
> If you have both a surrogate key (the serial column) as well as a
> natural key (e.g., the (first_name, last_name) composite key), what
> difference does it make? You can get to the first_name, last_name
> data via a join on the surrogate key.

The point is easy data management...

SELECT * FROM names;
UPDATE names set first_name = 'foo' WHERE id = 6
vs
SELECT * FROM names;
UPDATE names set first_name = 'foo' WHERE first_name = 'Joshua' AND
last_name = 'Drake';

Or did I misunderstand your question?

Joshua D. Drake

>
> I'm currently loosely in the surrogate key + natural key camp. One
> advantage of this is that if there *is* a change to the natural key,
> it can be changed in one table rather than cascading throughout the
> database schema.
>
> Michael Glaesemann
> grzm seespotcode net
>
> (Can we talk about NULL next? :P)
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate