Posted by Kenneth Downs on 05/15/06 02:16

Colin McKinnon wrote:

>
> It was fairly predictable that the issue of primary keys would come up.
> Dikkie Dik pointed out that using surrogate keys solves several issues
> in that the value is constant after creation (it also makes it simpler
> to adapt boilerplate scripts to different applications) however that
> has to be balanced against the long term costs caused by undermining
> the relational model. I have yet to see any compelling evidence for
> using a surrogate key over a natural one - I think Toby Inkster agrees
> with me there.
>

I've found in practice that surrogates only get you into trouble if you use
them as foreign keys. Can't offer any high-minded explanation for this, it
just seems to be the case. The practice is to have a natural primary key
that has some business meaning and also a surrogate key. The primary key
is used for foreign keys, the surrogate key is used only to simplify tasks
like updates and deletes after the row has been retrieved.

Every insert command also returns the resulting skey as a NOTICE, so you can
further manipulate the rwo if necessary.

Andromeda adds a surrogate key named "SKEY" for free to every table. This
makes it possible for the framework, once it has retrieved a set of rows,
to have very simple routines based column "SKEY" that do stuff like
page-by-page navigation, row selection, updates, and deletes.

Thanks also for your comments, I hope that you will find some use for
Andromeda.

--
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth@(Sec)ure(Dat)a(.com)

[Back to original message]