Hello hackers,
Oracle recently has added support to fourth order property constraints with the implementation of CREATE ASSERTION (
https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/create-assertion.html). It's a feature that I've wanted for a while, since the current workarounds for multigroup constraints (that is, in between relations or other objects at the database level) involve triggers, dubious usage of foreign keys and sometimes tags with checks in "subtyping", etc.
I can foresee that in a naive implementation of tracking all the objects that involve a constraint (likely how views do) and evaluating the constraint everytime something is modified on such objects, might incur into a performance hit for those using it, but I believe that it gives one extraordinary ergonomics, expressive and descriptive power that otherwise goes hidden with, say, triggers.
I would be willing to at least get started with a patch for this, but before that, I want to assess the interest and thoughts on how to properly implement it.
FWIW, this is part of the SQL Standard: ISO/IEC 9075-2:2023, 11.47:
<assertion definition> ::=
CREATE ASSERTION <constraint name>
CHECK <left paren> <search condition> <right paren>
[ <constraint characteristics> ]
If there's already a thread on the issue, forgive me, but I couldn't find it in the archives.
Regards!
Marcos Magueta.
