Re: Enforcing serial uniqueness?

Martijn van Oosterhout wrote:
> On Tue, Mar 21, 2006 at 11:41:11PM -0800, Steven Brown wrote:
>
>>I want to allow access to a table's rows without allowing that table to
>>be damaged. A problem I have is with my serial primary key 'id' field.
>> Although I can block its UPDATE, if users INSERT with an explicit 'id'
>>higher than the sequence, future INSERTs will fail due to the values
>>colliding. Ditto if users modify the sequence (setval) to be lower than
>>existing 'id' fields. This is rather bad, as it prevents
>>anyone/anything from INSERTing until fixed by manual intervention.
>
> IIRC you can set the permissions on a sequence to allow nextval but not
> setval.

Wouldn't it be possible to use nextval as default column value (what
serial types do) and only allow to select currval? I suppose that
depends on 'who' calls nextval when it's the default value; would be
nice if that'd be the sequence owner (more likely the table owner) in
this case.

That way they really can't touch the sequence; otherwise they still
could call nextval multiple times erroneously (people do that...). It
doesn't matter much to the sequence, of course... It just leaves the
ugly gaps out :P

Just shooting some arrows, I may miss the target.

--
Alban Hertroys
alban(at)magproductions(dot)nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //