Search Postgresql Archives

Re: Using varchar primary keys.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxx> wrote:
On Mon, Apr 1, 2013 at 1:22 AM, Jasen Betts <jasen@xxxxxxxxxx> 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@xxxxxxxxxxxxxx)
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 primary key, 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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux