From: Daryl Richter <daryl@eddl.us>
To: pgsql-sql@postgresql.org
Subject: Re: constraint and ordered value
Date: Thu, 29 Dec 2005 07:15:18 -0500
Message-ID: <0BC6CA83-B17E-47CC-8438-013D6B7348ED@eddl.us> (raw)
In-Reply-To: <20051229071625.GA2815@wolff.to>
References: <43B17F52.7000305@zara.6.isreserved.com>
<20051229071625.GA2815@wolff.to>
On Dec 29, 2005, at 2:16 AM, Bruno Wolff III wrote:
> On Wed, Dec 28, 2005 at 00:52:18 +0700,
> David Garamond <lists@zara.6.isreserved.com> wrote:
>> 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?
>
> No. A constraint only applies to one row at a time. If you try to
> work around
> this by calling a function that does queries it isn't guarenteed to
> work.
> And if you are thinking of calling a function that does a query,
> you aren't
> looking at saving time over using triggers.
>
> Also, if you are going to have concurrent updates, you are going to
> need to
> do table locking to make this work.
>
And, finally, you should ask yourself *why* are you doing this, given
that one of the fundamental properties of a table (relation) is that
the rows (tuples) are *unordered.* So much of what makes a
relational db a wonderful thing for storing data depends on this notion.
If you provide an explanation of what you are trying to model,
perhaps we can help you find a better schema design.
[snip]
--
Daryl
(setq email '( daryl at eddl dot us ))
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: daryl@eddl.us
Subject: Re: constraint and ordered value
In-Reply-To: <0BC6CA83-B17E-47CC-8438-013D6B7348ED@eddl.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox