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
