Обсуждение: [GENERAL] Is it possible to define a constraint based on the values in otherrows 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

> On 20 Jul 2017, at 5:26 AM, Hannes Erven <hannes@erven.at> wrote:
>
> Hi Glen,
>
>
>> I'd like to enforce that in a transaction, after a couple inserts &  > updates, a particular column has continuous
valueslike 1, 2, 3, and  
> > never any gaps. Is it possible to do?> > I gave a concrete example here: >
https://stackoverflow.com/questions/45187113didn't get any answers yet. 
> I've posted a comment to SO; basically what I'm saying is yes, use a transaction constraint trigger to enforce your
requirement.
>
> This previous question has an example of such a trigger:
>
> https://stackoverflow.com/a/37277421/1980653
>
>
> HTH & best regards
>
>     -hannes

Using a trigger sounds like the right approach. I’ll try it out. Thanks guys.

Hannes, thanks for posting an answer to SO, I’ve accepted it. :)