Recently I was playing again with my World Cup betting application, originally built in 2006 with Oracle APEX. Yes… almost 20 years ago already! 😅
Back then the database enforced some constraints, but many rules lived in the application layer (read APEX validation, processes, and PL/SQL). That was normal at the time.
But with Oracle Database 26ai and Assertions (introduced in 23.26.1) we can finally do something really nice:
Write complex business rules directly in the database using SQL.
No triggers.
No application checks.
Just declarative SQL.
I absolutely love this direction.
If you are new to assertions, check the official docs here:
And a great blog post from the Oracle SQL team:
https://blogs.oracle.com/sql/how-to-define-cross-table-constraints-with-assertions-in-oracle-ai-database
But instead of theory, I like real problems.
So let's look at a fun one.
The Problem: Prevent “Impossible Bets”
In the World Cup betting app, users predict match results.
The tables look like this:
W14_MATCH contains the official match info.
W14_BET contains the user's prediction.
Example:
| Match |
Prediction |
| Brazil vs Germany |
2-1 |
| Spain vs France |
0-0 |
Pretty simple.
But knockout matches introduce an interesting problem. A knockout match cannot end in a draw. If the regular score is equal, the winner is decided by extra penalties.
Our table supports this:
HOME_SCORE
VISIT_SCORE
HOME_EXTRA_PENALTIES
VISIT_EXTRA_PENALTIES
So a valid knockout bet could be:
2 - 2
Penalties: 5 - 4
But what should never happen is this:
2 - 2
Penalties: NULL - NULL
That means no winner.
And even worse:
2 - 2
Penalties: 3 - 3
Also impossible.
Until now this kind of rule normally lived in:
application validations
triggers
complex PL/SQL
But now we can express it as a single SQL rule.
The Assertion Solution
Let's define the rule:
If a bet predicts a draw, then the penalties must exist and produce a winner.
This involves multiple columns and conditional logic.
Perfect use case for an assertion.
Step 1. Create the Assertion
CREATE ASSERTION bet_knockout_must_have_winner
CHECK (
ALL (
SELECT b.home_score, b.visit_score,
b.home_extra_penalties, b.visit_extra_penalties
FROM w14_bet b
WHERE b.home_score = b.visit_score
) b
SATISFY (
b.home_extra_penalties IS NOT NULL
AND b.visit_extra_penalties IS NOT NULL
AND b.home_extra_penalties <> b.visit_extra_penalties
)
);
What does this do?
The assertion ensures that no row exists where:
The predicted score is a draw
AND penalties are missing or tied
If such a row appears, the database rejects the transaction.
Simple. Clear. Declarative.
Step 2. Let's Test It
Valid Bet
INSERT INTO w14_bet
(ID, USER_ID, MATCH_ID, HOME_SCORE, VISIT_SCORE,
HOME_EXTRA_PENALTIES, VISIT_EXTRA_PENALTIES,
CREATED_BY, CREATED_DATE)
VALUES
(1, 10, 5, 2, 2, 5, 4, 'DIMITRI', SYSDATE);
Result:
1 row inserted.
Good.
Now an invalid bet.
INSERT INTO w14_bet
(ID, USER_ID, MATCH_ID, HOME_SCORE, VISIT_SCORE,
HOME_EXTRA_PENALTIES, VISIT_EXTRA_PENALTIES,
CREATED_BY, CREATED_DATE)
VALUES
(2, 10, 5, 2, 2, NULL, NULL, 'DIMITRI', SYSDATE);
Result:
SQL Error: ORA-08601: SQL assertion (BET_KNOCKOUT_MUST_HAVE_WINNER) violated.
Help: https://docs.oracle.com/error-help/db/ora-08601/
More Details :
https://docs.oracle.com/error-help/db/ora-08601/
Exactly what we want. The database protects the integrity of the data.
Why This Matters
This might look like a small rule, but in real systems things get complicated quickly.
Think about rules like:
A building cannot consume more power than the grid supplies
A hospital cannot schedule more patients than rooms available
A betting pool must have exactly one winner per match
A smart city cannot exceed environmental limits
These rules often span multiple tables or conditional logic.
Before assertions we had to rely on:
triggers
application validations
batch data cleanup
Now we can express them as true data integrity rules.
Readable SQL. Centralized. Guaranteed.
I really like that.
Why I Love This Feature
Assertions make the database smarter.
And honestly, the Oracle Database has always been the best place for business rules.
We are just getting better tools to express them.
When I look back at the APEX app I built in 2006, many rules were in PL/SQL and page validations. If I would rebuild it today, a lot of that logic would move to assertions.
Cleaner architecture. Safer data. Less code. And that is always a good thing.
If you haven't played with Assertions in Oracle Database 26ai, go and try them out.
If you don't have a local DB and you want to try it, Oracle FreeSQL is very cool:
(note: at the time of writing the environment of FreeSQL was still 23.26, while you need 23.26.1 to get assertions to work)
