Re: Better alternative for Primary Key then serial??

On Dec 13, 2007, at 10:19, Jorge Godoy wrote:

> Em Wednesday 12 December 2007 03:42:55 pilzner escreveu:
>>
>> Does stuff like this cause any aches and pains to developers out
>> there, or
>> do I just need to get in a new mindset??? Also, is there a way to
>> be sure
>> the primary key is *ONLY* ever given a value by serial, and not
>> subject to
>> updates???
>
> Shouldn't the definition of a primary key be an immutable thing
> that is unique
> to the row? If you change it, then it is not immutable anymore...

Why? If you're worried about foreign keys pointing to them, you can
either define them to follow the change (on update cascade) or to
throw an integrity violation error (default behaviour).

Now I realise this isn't particularly useful for surrogate (primary)
keys, but it sure is for natural keys. They're both keys, is there a
reason to handle surrogate keys differently from natural keys?

The problem the OP is pointing out seems difficult to solve. A
sequence doesn't know about existing records with a possibly higher
number than the sequence is at.

This may be worked around by keeping a list of numbers used up beyond
the current sequence value so the sequence knows what numbers to
skip, but that has problems of its own (if there are many such
numbers, or if the sequence gets created after data has been added to
the list). It gets ugly.

The convention with sequences is that if you use a sequence on a
column (beyond defining one) that you don't insert records with hand-
coded values for that column (unless you're sure you're using an
existing gap before the sequences current value).

Regards,
--
Alban Hertroys

Sometimes you wake up thinking:
"Galileo was right, the world does turn"

!DSPAM:737,476112479655680816383!