Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 264BC9FA0EF for ; Wed, 13 Dec 2006 10:56:13 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 60975-02 for ; Wed, 13 Dec 2006 10:55:59 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from coe.tamu.edu (edsun.coe.tamu.edu [165.91.232.6]) by postgresql.org (Postfix) with ESMTP id 42F659FA162 for ; Wed, 13 Dec 2006 10:55:59 -0400 (AST) Received: (from root@localhost) by coe.tamu.edu (8.11.7p2+Sun/8.12.11) id kBDEsva06758 for pgsql-admin@postgresql.org; Wed, 13 Dec 2006 08:54:57 -0600 (CST) Received: from coe.tamu.edu (coe.tamu.edu [165.91.232.6]) by coe.tamu.edu (8.11.7p2+Sun/8.12.11) with ESMTP id kBDEsMC06743 for ; Wed, 13 Dec 2006 08:54:22 -0600 (CST) Date: Wed, 13 Dec 2006 08:54:22 -0600 (CST) From: "Ben K." To: pgsql-admin@postgresql.org Subject: Re: How to enforce the use of the sequence for serial columns Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed X-Virus-Scanned: by AMaViS 0.3.12 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/185 X-Sequence-Number: 23977 "Marc Mamin" 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