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@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 ))