X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 4123E9DC84F for ; Tue, 27 Dec 2005 13:59:12 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 60642-10 for ; Tue, 27 Dec 2005 13:59:11 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from 10.hostcorporate.com (unknown [69.41.231.186]) by postgresql.org (Postfix) with SMTP id 10DF19DC844 for ; Tue, 27 Dec 2005 13:59:09 -0400 (AST) Received: (qmail 3901 invoked from network); 27 Dec 2005 17:30:18 -0000 Received: from localhost (HELO localhost.localdomain) (127.0.0.1) by 10.hostcorporate.com with SMTP; 27 Dec 2005 17:30:18 -0000 Message-ID: <43B17F52.7000305@zara.6.isreserved.com> Date: Wed, 28 Dec 2005 00:52:18 +0700 From: David Garamond User-Agent: Debian Thunderbird 1.0.7 (X11/20051017) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: constraint and ordered value Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200512/208 X-Sequence-Number: 23750 Is it possible to use only CHECK constraint (and not triggers) to completely enforce ordered value of a column (colx) in a table? By that I mean: 1. Rows must be inserted in the order of colx=1, then colx=2, 3, and so on; 2. When deleting (or updating), "holes" must not be formed, e.g. if there are three rows then row with colx=3 must be the first one deleted, and then colx=2 the second, and so on. I can see #1 being accomplished using a NOT NULL + UNIQUE constraint and a CHECK constraint that calls some PL function where the function does a simple checking (new.colx=1 if COUNT(colx)==0, or new.colx=MAX(colx)+1). But is it possible to do #2 using only constraints? Regards, dave