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