Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id C93859FA37A for ; Thu, 14 Dec 2006 08:31:05 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 73776-01 for ; Thu, 14 Dec 2006 08:30:44 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mama.jerrysievers.com (adsl-64-118-249-70.netrox.net [64.118.249.70]) by postgresql.org (Postfix) with ESMTP id 542A99FA324 for ; Thu, 14 Dec 2006 08:30:53 -0400 (AST) Received: from homie.jerrysievers.com (homie.jerrysievers.com [192.168.1.7]) by mama.jerrysievers.com (8.13.7/8.13.7) with ESMTP id kBECUko1032291; Thu, 14 Dec 2006 07:30:46 -0500 Received: from homie.jerrysievers.com (localhost.localdomain [127.0.0.1]) by homie.jerrysievers.com (8.13.7/8.13.7) with ESMTP id kBECUkaW009742; Thu, 14 Dec 2006 07:30:46 -0500 Received: (from jerry@localhost) by homie.jerrysievers.com (8.13.7/8.13.7/Submit) id kBECUk9F009737; Thu, 14 Dec 2006 07:30:46 -0500 To: "Ben K." Cc: pgsql-admin@postgresql.org Subject: Re: How to enforce the use of the sequence for serial columns References: From: Jerry Sievers Date: 14 Dec 2006 07:30:46 -0500 In-Reply-To: Message-ID: Lines: 81 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.4 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/201 X-Sequence-Number: 23993 "Ben K." writes: > > create table mytable (myid serial primary key constraint > mytable_myid_chk check (myid = currval('mytable_myid_seq'), mydata > varchar(255), ...); > > > I'd like to clarify that this will not be a full solution, since it > will not allow update of the table unless nextval has been used in the > same sequence already. It's not a full solution anyway since it prevents any kind of update on the table due to check constraints firing even if target field not updated. See below; jerry@jerry# = create table foo (a serial check (a = currval('foo_a_seq')), b int); NOTICE: CREATE TABLE will create implicit sequence "foo_a_seq" for serial column "foo.a" CREATE TABLE jerry@jerry# = insert into foo values (default, 1); INSERT 0 1 jerry@jerry# = insert into foo values (default, 1); INSERT 0 1 jerry@jerry# = insert into foo values (default, 1); INSERT 0 1 jerry@jerry# = insert into foo values (default, 1); INSERT 0 1 jerry@jerry# = select * from foo; a | b ---+--- 1 | 1 2 | 1 3 | 1 4 | 1 (4 rows) jerry@jerry# = update foo set b=2; ERROR: new row for relation "foo" violates check constraint "foo_a_check" jerry@jerry# = > > There seems to be a hack in case of oracle that allows using currval > without nextval, but it's a hack and I don't know if there's an > equivalent in postgresql > (http://rootshell.be/~yong321/computer/sequence.txt). (Oracle seems to > have "disable" option when adding check constraint by alter table, but > the context seem a bit different from ours so may not be useful in our > case, at any rate.) > > > > > Regards > > Ben K. > Developer > http://benix.tamu.edu > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > -- ------------------------------------------------------------------------------- Jerry Sievers 305 854-3001 (home) Production Database Administrator 305 321-1144 (mobil WWW E-Commerce Consultant