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.
