Re: Changing ids conflicting with serial values?

Tom Lane wrote:
> Steven Brown <swbrown(at)ucsd(dot)edu> writes:
>> When I change an id (primary key serial) in a table, the next value
>> returned by the sequence for the id can conflict with that id (e.g.,
>> change the id to be id + 1).
[...]
> Plan A: don't do that. Why in the world is it a good idea to modify an
> artificial primary key? It's not like there's some external meaning to
> the values.

I'm granting access to insert/update/delete rows of a table to people,
but I don't want all future inserts to fail if they decided to change an
id (which they obviously shouldn't, but they /can/). It makes for a
fragile system.

Should I just be using some sort of trigger to block them from modifying
the id, or is there another way to handle it? I.e., how do people
normally handle that? It's a migration thing - MySQL prevented this
situation due to the way it handles auto_increment (it will never assign
you an id that already exists).