Re: Changing ids conflicting with serial values?

Steven Brown <swbrown(at)ucsd(dot)edu> writes:

> 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.

create rule no_pkey_update
as on update to foo where new.id != old.id
do instead nothing;

Simple minded solution with negative aspect that it will silently skip
ANY update trying to change pkey... other changes to record also
discarded.

> 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).

Bit more complex but still easy is trigger to just always set new.id
to old.id thereby insuring that it can't be changed.

create function no_pkey_update()
returns trigger
as '
begin
new.id = old.id;
return new;
end'
language plpgsql;

HTH

--
-------------------------------------------------------------------------------
Jerry Sievers 305 854-3001 (home) WWW ECommerce Consultant
305 321-1144 (mobile http://www.JerrySievers.com/