Re: How to enforce the use of the sequence for serial columns

"Marc Mamin" <M(dot)Mamin(at)intershop(dot)de> writes:
> I'd like to ensure that nobody provide the ID in an insert statement
> when the id is linked to a sequence.
> I tried it with a trigger, but the id value is fed before the "BEFORE
> INSERT" test is performed (see below)...

If you have the luxury of re-creating the table (dump, edit schema,
reload), you might want to try this:

create table mytable (myid serial primary key constraint mytable_myid_chk check (myid =
currval('mytable_myid_seq'), mydata varchar(255), ...);

This will prevent "serial" id field from being arbitrarily manipulated
outside the sequence..

There may be an equivalent way using alter table, but on a first look, currval
cannot be used to check existing values.

Regards

Ben K.
Developer
http://benix.tamu.edu