Re: constraint and ordered value

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(at)zara(dot)6(dot)isreserved(dot)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 ))