On Wed, Oct 18, 2023 at 7:43 AM Thomas Kellerer <shammat@gmx.net> wrote:
Merlin Moncure schrieb am 18.10.2023 um 03:20: > The only thing you can't really do in SQL easily without writing > nasty triggers are things like, 'this table must be linked from one > and only one of these candidate tables'. I think the language > probably ought to support this, but I don't think postgres would > unless the standard did.
Isn't that what assertions are intended to solve in the SQL standard?
Hm, interesting, <googles> ...maybe, maybe not. SQL '92 -- wow. guessing why not: Are they deferrable, always deferred? Doesn't seem like it, simple inheritance cases would seem problematic otherwise, chicken/egg conditions. This might explain why they are not in common use despite being innovated 30 years ago. Here is an Oracle discussion on the topic:
...with the discussion implying that interaction with the transaction state may be an unsolved problem, at least within oracle. I suspect there may also be challenges relating to performant implementation. Sadly, I think the correct approach remains to utilize complex triggers, or nothing.