Re: Using varchar primary keys.

On 02/04/13 08:35, jesusthefrog wrote:
> On the topic of 'natural' versus 'synthetic' primary keys, I am
> generally in the camp that an extra ID field won't cost you too much,
> and while one may not need it for a simple table (i.e. id, name) one
> might add any number of columns later, and you'll be glad to have it.
>
> I am, however, against using sequences (or serial integers in
> Postgres) for reasons of scaling and replication across multiple
> copies of a database running on different servers.
>
> My preferred method is to give every table an ID column of UUID type
> and generate a UUID using the uuid-ossp contrib module. This also
> prevents someone not familiar with the database design from using an
> ID somewhere they should not (as is possible with natural PKs) or
> treating the ID as an integer, not an identifier (as is all too common
> with serial integers).
>
>
>
> On Mon, Apr 1, 2013 at 9:35 AM, Merlin Moncure <mmoncure(at)gmail(dot)com
> <mailto:mmoncure(at)gmail(dot)com>> wrote:
>
> On Mon, Apr 1, 2013 at 1:22 AM, Jasen Betts <jasen(at)xnet(dot)co(dot)nz
> <mailto:jasen(at)xnet(dot)co(dot)nz>> wrote:
> > On Mon, Apr 01, 2013 at 07:08:15PM +1300, Tim Uckun wrote:
> >> >
> >> > how about using an enum instead of this table?
> >> >
> >> >
> >> That's an interesting idea. Are enums mutable?
> >
> >
> > since 9.1 you can add values.
> >
> > http://www.postgresql.org/docs/9.1/static/sql-altertype.html
>
> It's an interesting idea, but I don't think enums are designed to act
> as a primary key except in cases where the data is basically static
> and is relatively small. For starters, any manipulation of the enum
> requires a lock.
>
> enums can be a real life saver when you need custom ordering built
> into a string, especially if that ordering is floated over a composite
> index.
>
> merlin
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org
> <mailto:pgsql-general(at)postgresql(dot)org>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>
>
>
> --
> -----BEGIN GEEK CODE BLOCK-----
> Version: 3.12
> GIT d+ s: a-- C++++(++) UL+++ P++++$ L+++>++++ E- W+ N o? K w--- O-
> M- V? PS+++ PE(-) Y+ PGP->+++ t+>++ 5+++++ X(+) R>+ tv b+
> DI++ D++>+++ G+ e* h! !r y**
> ------END GEEK CODE BLOCK------
I use synthetic primary keys, I want to minimise changes to the database
because the user, directly or due to of some law change, changes a
'natural' value. Using synthetic primary keys, minimises changes to a
database when a 'natural' value changes - if people's names are part of
many natural keys, then when people change their name (like when a woman
gets married), only one table needs to change. Likewise the customer
nunber the manager swore would never change, now they want to change for
a numeric key to an alphanumeric one.

Using 'natural' values for a primarykey, seems to be deliberately adding
potential time bombs. Almost as bad as the misguided idea that using
'sudo' is safer than the alternative when executing commands a root!

Cheers,
Gavin