On Wed, Oct 18, 2023 at 7:43 AM Thomas Kellerer <sham...@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: https://forums.oracle.com/ords/apexds/post/sql-assertions-declarative-multi-row-constraints-8418 ...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. merlin
