Re: surrogate vs natural primary keys

At 08:20 AM 9/18/2008, pgsql-sql-owner(at)postgresql(dot)org wrote:
>Message-ID:
><396486430809170920o12782b5dy3837b4aa8611a0c1(at)mail(dot)gmail(dot)com>
>Date: Wed, 17 Sep 2008 09:20:44 -0700
>From: "Richard Broersma" <richard(dot)broersma(at)gmail(dot)com>
>To: "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>
>Subject: Re: surrogate vs natural primary keys
>In-Reply-To:
><dcc563d10809170745n24f5ae73i7e6ba3ec73743dbc(at)mail(dot)gmail(dot)com>
>References: <87y71t2hlu(dot)fsf(at)patagonia(dot)sebmags(dot)homelinux(dot)org>
> <dcc563d10809151545r70d7d2a7j25931d7557dd88f3(at)mail(dot)gmail(dot)com>
> <396486430809160759n15ec2134ke0df317f36f74d5a(at)mail(dot)gmail(dot)com>
> <20080916151743(dot)GG201(at)commandprompt(dot)com>
> <87ljxrvdhw(dot)fsf(at)patagonia(dot)sebmags(dot)homelinux(dot)org>
> <dcc563d10809161934q205e4e3bu6b9cd43339ff89b3(at)mail(dot)gmail(dot)com>
> <87abe6vrgl(dot)fsf(at)patagonia(dot)sebmags(dot)homelinux(dot)org>
> <dcc563d10809170745n24f5ae73i7e6ba3ec73743dbc(at)mail(dot)gmail(dot)com>
>X-Archive-Number: 200809/124
>X-Sequence-Number: 31576
>
>
>My opinion is that the database constraints are the last line of
>defense to ensure business rules and data integrity are not violated.
>Since I highly value the ability to enforce business rules using
>ordinary table DDL, I try to use natural keys as often as I can.
>

Hi Richard,

I often find your comments insightful and right on the money. This is
another one of those cases. Your comments above are a great example of
when natural keys make sense: I hadn't looked at it from this
perspective!

I'm a middleware developer (the bane of DBA's!) -- and so I generally
"solve" these sorts business rules constraints in the middleware code,
which of course is prone to all kinds of different problems (like lazy
developers who code around the OO validation checkers!).

Thanks for giving such a great explanation as to the value of natural
keys! You haven't won me over, but you did teach me something - which I
appreciate.

Best,

Steve