On Dec 29, 2005, at 2:16 AM, Bruno Wolff III wrote:
On Wed, Dec 28, 2005 at 00:52:18 +0700,
David Garamond <[EMAIL PROTECTED]> 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 ))
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
