By Dênio Flávio Garcia da Silva
What got me into assertions was not a greenfield use case, and it was not some academic curiosity about new syntax. It was the opposite. It was the uncomfortable realization that I had already needed this kind of feature in several situations, long before it actually existed.
In projects involving ERP replacement and data migration, I have worked with data coming from different databases, different schemas, different naming standards, and very different levels of discipline around business rules. On paper, the model usually looked acceptable. In practice, the integrity was often scattered across application code, ad hoc validations, migration scripts, or trigger logic that somebody had written years earlier to patch a hole that should probably have been closed at schema level from the beginning.
That is why assertions caught my attention so quickly.
Last week I created an Oracle 26ai database in my OCI account and started exploring a few features from this version that I had not touched yet. Assertions were one of the first that made me stop and think about old project scars. If you want to try the same path, Oracle’s official Free Tier and Always Free Autonomous AI Database pages are a good starting point for spinning up your own environment before reproducing the examples below. (Oracle)
Oracle defines assertions as boolean expressions with constraint semantics. The database is responsible for ensuring that these expressions remain true while transactions change and commit data. That immediately puts them in a different category from ordinary procedural fixes, because the point is not reacting to an event. The point is declaring that a business invariant belongs to the schema and must hold there.
This post is not about arguing that assertions replace triggers or applications code. It is about showing how clean they feel when the rule really belongs to the model.
I will keep it practical.
First, I will create a small schema around products, prices, and customers. Then I will create assertions in both styles Oracle supports: the classic existential form based on EXISTS / NOT EXISTS, and the universal form using ALL ... SATISFY. After that, I will deliberately run statements that violate the rule, and then the corrected statements that satisfy it. Oracle documents both styles as valid ways to express the same integrity rule, and in practice this makes for a nice side-by-side demo.
Base objects
Let’s create the base objects:
create table products (
product_id number primary key,
product_name varchar2(200) not null
);
create table product_prices (
price_id number primary key,
product_id number not null,
start_date date not null,
end_date date,
price_amount number(12,2) not null,
reason_text varchar2(4000),
constraint fk_product_prices_product
foreign key (product_id)
references products(product_id),
constraint ck_product_prices_period
check (end_date is null or end_date >= start_date)
);
create table customers (
customer_id number primary key,
customer_name varchar2(200) not null
);
create table customer_addresses (
address_id number primary key,
customer_id number not null references customers(customer_id),
address_text varchar2(300) not null
);
create table customer_phones (
phone_id number primary key,
customer_id number not null references customers(customer_id),
phone_number varchar2(30) not null
);
create table customer_emails (
email_id number primary key,
customer_id number not null references customers(customer_id),
email_address varchar2(300) not null
);
Execution (click to zoom in):
Demo 1: every customer must have address, phone, and email
This is the kind of rule I have seen get pushed into application code during migration projects, usually because each piece of customer data lives in a different table and somebody decides to validate it “later”. That works until later never comes.
Existential expression
create assertion customer_must_have_address
check (
not exists (
select 'customer without address'
from customers c
where not exists (
select 'address'
from customer_addresses ca
where ca.customer_id = c.customer_id
)
)
) deferrable initially deferred;
create assertion customer_must_have_phone
check (
not exists (
select 'customer without phone'
from customers c
where not exists (
select 'phone'
from customer_phones cp
where cp.customer_id = c.customer_id
)
)
) deferrable initially deferred;
create assertion customer_must_have_email
check (
not exists (
select 'customer without email'
from customers c
where not exists (
select 'email'
from customer_emails ce
where ce.customer_id = c.customer_id
)
)
) deferrable initially deferred;Execution:
Now the intentional violation:
insert into customers (customer_id, customer_name)
values (1, 'APXFF COMPANY');
commit;
That should fail because the customer exists without the three mandatory related records. As you can see, it raises ORA-08601 exception:
Now the valid path:
insert into customers (customer_id, customer_name)
values (1, 'APXFF COMPANY');
insert into customer_addresses (address_id, customer_id, address_text)
values (1, 1, '100 Main Street');
insert into customer_phones (phone_id, customer_id, phone_number)
values (1, 1, '+1 555 0100');
insert into customer_emails (email_id, customer_id, email_address)
values (1, 1, 'contact@acme.example');
commit;
And now we have de rows inserted correctly:
These inserts are possible due to DEFERRABLE INITIALLY DEFERRED statement when creating the assertions. That means the assertion is allowed to be checked at commit time instead of after each individual statement, so the transaction can temporarily violate the rule while you build a valid final state.
Universal expression
Another way to write the same rule written with ALL ... SATISFY:
create assertion customer_must_have_minimum_contact_data_u
check (
all (
select c.customer_id
from customers c
) cu
satisfy (
exists (
select 'address'
from customer_addresses ca
where ca.customer_id = cu.customer_id
)
)
);I like this version because the rule reads much closer to how the business would actually say it. Oracle’s documentation calls out this exact benefit of universal expressions: they reduce the amount of negation needed in some assertions.
Demo 2: a price reduction must have a reason
This one is closer to the kind of thing that used to fall straight into trigger code in many systems.
The point here is not that a trigger would be wrong. The point is that this rule is still an integrity rule, and it is interesting to see it declared that way.
create assertion price_reduction_requires_reason
check (
all (
select current_price.product_id,
current_price.start_date,
current_price.price_amount as current_amount,
previous_price.price_amount as previous_amount,
current_price.reason_text
from product_prices current_price,
product_prices previous_price
where current_price.product_id = previous_price.product_id
and previous_price.end_date = current_price.start_date - 1
) px
satisfy (
px.current_amount >= px.previous_amount
or px.reason_text is not null
)
);The execution:
First, create a product and an initial price window:
insert into products (product_id, product_name)
values (20, 'OIL FILTER');
insert into product_prices (
price_id,
product_id,
start_date,
end_date,
price_amount,
reason_text
) values (
200,
20,
date '2026-01-01',
date '2026-01-31',
50.00,
'Initial price'
);
commit;
Execution:
Now the violating operation:
insert into product_prices (
price_id,
product_id,
start_date,
end_date,
price_amount,
reason_text
) values (
201,
20,
date '2026-02-01',
null,
45.00,
null
);
commit;
That should fail because the new period reduces the price and provides no reason:
Correct version:
insert into product_prices (
price_id,
product_id,
start_date,
end_date,
price_amount,
reason_text
) values (
201,
20,
date '2026-02-01',
null,
45.00,
'Seasonal campaign adjustment'
);
commit;And now the row is inserted without error:
This sequence moves the discussion away from “parent must have child” and into a rule about business meaning.
To wrap up
What I like about assertions is not that they make triggers obsolete, because that would be a lazy conclusion. What I like is that they give the schema a more direct way to carry business truth.
And if you have spent enough time dealing with migrations, ERP changes, and data that came from too many places with too few guarantees, you know that every rule pushed back into the model itself is one less rule waiting to be forgotten in the wrong layer.
Deixe um comentário