Re: Order of update

On 2025-04-21 18:12:13 +0200, Thiemo Kellner wrote:
> I wonder if that is a corner case. Updating a unique key sounds to me like a design flaw in the first place.

I agree that changing a surrogate key is almost always a mistake.

But there might be situations where a column should be unique but isn't
an id.

For example, many years ago it was a popular[1] programming pattern to
represent trees as nested ranges (i.e. if two children of a parent had
the ranges (a, b) and (b+1, c) then the parent had (a-1, c+1).
Insert-operations then need to update those columns. You want an index
on those columns (since you search for them a lot), and you might want
to make it a unique index, since that covers part of the invariant
(although not the complete invariant). If you do that you run into the
update problem.

There are probably other use-cases. Anything where you need a unique
order which can change, I guess?

Anyway, I don't have a pressing need for this, as I said I was just
curious.

hjp

[1] Mostly in MySQL I think, since it didn't have recursive queries of
any kind.

--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"