Re: Is it possible to define a constraint based on the values in other rows in the current table?

On 2017-07-19 13:37, Glen Huang wrote:
> Hi,
>
> I'd like to enforce that in a transaction, after a couple inserts &
> updates, a particular column has continuous values like 1, 2, 3, and
> never any gaps. Is it possible to do?
>
> I gave a concrete example here:
> https://stackoverflow.com/questions/45187113 didn't get any answers
> yet.
>
> Am I looking at the wrong direction? Should such feature be
> implemented with constraints?
>
> Thanks.

If the value is only used for sorting then the exact value doesn't
matter,
only that there are nu duplicates. 4,5,6 orders the exact same way as
1,2,3 or 500,540,615

You are guaranteed to get gaps anyway when you remove a record.

Personally I'd sooner create a trigger that generates a new value on
INSERT, and that
(if you *really* feel a need to) can fix gaps on DELETE and UPDATE