Temporal SQL Assertions I: Unconditional and Unvalidated

Anthony Harper

March 15, 2026

			
set serveroutput on;
declare
    procedure what_to_choose(true_today in boolean, true_always in boolean)
    is
        dynamic_data boolean;
    begin
        dynamic_data := true_today and not true_always;
    
        dbms_output.put_line(
            'When data is ' || case when dynamic_data then 'dynamic' else 'static' end
            || ' and rules are inflexible then ' 
            || case when not dynamic_data then 'universal assertions are great' 
                else 'universal assertions have issues' end);
    end what_to_choose;
begin
    what_to_choose(true_today => true, true_always => true);
    what_to_choose(true_today => true, true_always => false);
end;
/

		

In this series of posts we will explore using assertions to enforce business rules in a changing data landscape. This first article explores the limitations of a simplistic approach to a problem that is more complex than it first appears to be.

SQL assertions allow for complex cross table data integrity rules to be implemented at the schema level. This brings new abilities to apply business rules that will be enforced without requiring that all editing of the data uses api code which takes care of these business rules. Application code does not have to be changed to account for enforcing business rules, everything can be done in the database regardless of the manner in which data changes are applied.

Getting Started

The scripts for all examples in this post are available in the practicalplsql repository on github at effective-dated-assertions-1. I will add direct links to script files when first referenced.

SQL Assertions are first available in Oracle AI Database version 26 release 1. At the time of this writing, this database is only available in OCI or as a container image. The VirtualBox appliance for Oracle Free is not yet available. I created an appliance with Oracle Linux 8 and Docker with Compose for local testing of assertions code.

If you are working on OCI, just grant the create assertion privilege to the user you are testing with:

grant create assertion to practicalplsql;

This is the compose.yaml file I used to set up the latest oracle free container, I have set restart: unless-stopped to allow restarting the linux virtual machine running docker without having to manually restart the container.

compose.yaml

  services:
    # name of the Docker Compose service
    db:
      # Docker hub image
      image: gvenzl/oracle-free:latest
      # make container restart on host reboot
      restart: unless-stopped
      # forward container port to localhost
      ports: 
        - "1521:1521"
      # set environment variables in the container
      environment:
        ORACLE_PASSWORD: oracle
        APP_USER: practicalplsql
        APP_USER_PASSWORD: oracle
      # mount init-scripts to initialize schema   
      volumes:
        - ./initdb.d/setup:/container-entrypoint-initdb.d
        - ./initdb.d/startup:/container-entrypoint-startdb.d

In the project directory, I created subdirectories /initdb.d/setup/ and placed the following script so that it is executed when creating the container:

./initdb.d/setup/01-create-users.sql

			
alter session set container=FREEPDB1;
create user if not exists practicalplsql identified by oracle;
alter user practicalplsql quota unlimited on users;
grant connect, db_developer_role to practicalplsql;
grant create assertion to practicalplsql;

		

Use Case: Customer Loyalty Program Discounts

The company is implementing their new customer loyalty program with guaranteed minimum discounts on customer orders for each loyalty level achieved.

There will be several customer loyalty levels defined, each with a minimum order discount requirement: “New” customers have no minimum discounts, “Preferred” customers receive a 5% discount and “Elite” customers always get a 10% discount.

Customers will start as “New“, and then get upgraded to “Preferred” and “Elite” as they place more orders.

Over time, the minimum required discounts for the various loyalty levels will be changed.

Rather than enforcing the loyalty discounts through application code, the company has decided to use the new SQL assertions feature in Oracle 26.1 to ensure that orders meet the minimum loyalty discounts for the customer.

Tables

The data model consists of tables for loyalty, customers and orders. The loyalty table has a minimum discount for each loyalty status. The customers table holds the loyalty status of the customer. The orders table has each customer order with a discount level.

example-1-01-tables-initial.sql

			
create table if not exists loyalty(
    status varchar2(10) 
        constraint loyalty_pk primary key,
    discount_min number(5,4) default 0 not null
)
/
prompt insert the discount minimum for each loyalty status
begin
    insert into loyalty(status, discount_min)
    values ('New', 0), ('Preferred', .05), ('Elite', 0.10);
    commit;
end;
/
prompt note: customers.status is nullable
create table if not exists customers(
    customer_name varchar2(10) 
        constraint customers_pk primary key,
    status varchar2(10) default 'New' 
        constraint customers_fk_loyalty 
        references loyalty(status) 
)
/
create table if not exists orders(
    order_id integer generated always as identity 
        constraint orders_pk primary key,
    customer_name varchar2(10)
        constraint orders_fk_customers
        references customers(customer_name) not null,
    discount number(5,4) default 0 not null,
    placed date default sysdate not null
)
/

		

The customers.status column is nullable in the initial tables, indicating that a loyalty status is optional.

Assertion

The assertion simply has to look for the existence of a customer order that has a discount percentage smaller than the customer loyalty level.

example-1-02-assertion-initial.sql

			
create assertion if not exists loyalty_discount_applied check (
    not exists (
        select 'order discount invalid for customer loyalty status'
        from 
            orders o, 
            customers c, 
            loyalty s
        where 
            o.customer_name = c.customer_name 
            and c.status = s.status
            and o.discount < s.discount_min
    )
)
/
ORA-08689: CREATE ASSERTION failed
ORA-08673: Equijoin "C"."STATUS"="S"."STATUS" found does not meet the criteria to do a FAST validation.

		

There is nothing in the documentation regarding criteria for a ‘FAST’ validation. After some experimentation I found that the assertion join has to use columns that are not nullable.

			
alter table customers modify status not null;
Table CUSTOMERS altered.
create assertion if not exists loyalty_discount_applied check (
    not exists (
        select 'order discount invalid for customer loyalty status'
        from 
            orders o, 
            customers c, 
            loyalty s
        where 
            o.customer_name = c.customer_name 
            and c.status = s.status
            and o.discount < s.discount_min
    )
)
/
Assertion LOYALTY_DISCOUNT_APPLIED created.

		

Now the assertion is in place and customers should always get the minimum discount determined by their loyalty status.

Create View For Verifying Discounts

During testing, we will want to see if the orders that were successfully placed had valid discounts. Create a view for reviewing orders.

example-1-03-view.sql

			
create or replace view review_order_discounts as
select 
    c.customer_name
    , c.status
    , (100 * s.discount_min) || '%' as loyalty_discount
    , o.order_id
    , (100 * o.discount) || '%' as order_discount
    , case 
        when o.discount < s.discount_min then 'Insufficient' 
        when o.discount = s.discount_min then 'Meets Minimum' 
        else 'Exceeds Minimum' 
    end as discount_valid 
from 
    customers c 
    join loyalty s on c.status = s.status
    join orders o on c.customer_name = o.customer_name
order by c.customer_name, o.order_id
/

		

Testing Customer Orders

Create a customer for each loyalty level and test placing valid orders.

example-1-05-tests-sql.sql

			
set serveroutput on;
begin
    dbms_output.put_line('#Create customer Nina, status New');
    insert into customers (customer_name, status) values ('Nina', 'New');
    dbms_output.put_line('#Place valid orders: Nina, New, [0, 0.05]');    
    insert into orders(customer_name, discount)
    values('Nina', 0), ('Nina', 0.05);
    dbms_output.put_line('#Create customer Prue, status Preferred');
    insert into customers (customer_name, status) values ('Prue', 'Preferred');
    
    dbms_output.put_line('#Place valid order: Prue, Preferred, 0.05');
    insert into orders(customer_name, discount)
    values('Prue', 0.05);
    
    dbms_output.put_line('#Create customer Liza, Elite');
    insert into customers(customer_name, status) values('Liza', 'Elite');
    
    dbms_output.put_line('#Place valid orders: Liza, Elite, [0.10,0.11]');
    insert into orders(customer_name, discount)
    values('Liza', 0.10),('Liza', 0.11);
    
    commit;
end;
/
#Create customer Nina, status New
#Place valid orders: Nina, New, [0, 0.05]
#Create customer Prue, status Preferred
#Place valid order: Prue, Preferred, 0.05
#Create customer Liza, Elite
#Place valid orders: Liza, Elite, [0.10,0.11]

		

The assertion is working for valid orders. Now lets test creating orders with insufficient discounts.

			
begin
    dbms_output.put_line('#Place invalid order: Prue, Preferred, 0');
    insert into orders(customer_name, discount)
    values('Prue', 0);
exception
    when others then
        rollback;
        dbms_output.put_line(sqlerrm);
end;
/
#Place invalid order: Prue, Preferred, 0
ORA-08601: SQL assertion (PRACTICALPLSQL.LOYALTY_DISCOUNT_APPLIED) violated.
begin
    dbms_output.put_line('#Place invalid order: Liza, Elite, 0.05');
    insert into orders(customer_name, discount)
    values('Liza', 0.05);
exception
    when others then
        rollback;
        dbms_output.put_line(sqlerrm);
end;
/
#Place invalid order: Liza, Elite, 0.05
ORA-08601: SQL assertion (PRACTICALPLSQL.LOYALTY_DISCOUNT_APPLIED) violated.

		

The attempts to place orders with discounts that are lower than the customer status minimum all result in violations of the assertion. The assertion is working as designed.

Testing Upgrading Customer Loyalty Status

Customer loyalty levels change over time, lets see what happens when we try to upgrade Nina to “Preferred” loyalty status:

			
begin
    dbms_output.put_line('#Upgrade Nina to Preferred status with assertion enabled');
    update customers
    set status = 'Preferred' 
    where customer_name = 'Nina';
exception
    when others then
        rollback;
        dbms_output.put_line(sqlerrm);
end;
/
#Upgrade Nina to Preferred status with assertion enabled
ORA-08601: SQL assertion (PRACTICALPLSQL.LOYALTY_DISCOUNT_APPLIED) violated.

		

The customer status cannot be changed without violating the assertion! The first order Nina placed as a “New” customer had no discount, and can no longer be validated at the new status of Preferred which requires a minimum 5% order discount. This is not really how the business expected the rules to work. Customers should be able to upgrade their status over time without issues.

Just like constraints, assertions can be created or altered in a novalidate state. If we enable the assertion as novalidate, only new data will be validated by the assertion and existing data will not be validated. Put the assertion into a novalidate state and retry the status upgrade for Nina:

			
alter assertion loyalty_discount_applied enable novalidate;
Assertion LOYALTY_DISCOUNT_APPLIED altered.
begin
    dbms_output.put_line('#Upgrade Nina to Preferred status with assertion enabled novalidate');
    update customers
    set status = 'Preferred' 
    where customer_name = 'Nina';
exception
    when others then
        rollback;
        dbms_output.put_line(sqlerrm);
end;
/
#Upgrade Nina to Preferred status with assertion enabled novalidate
ORA-08601: SQL assertion (PRACTICALPLSQL.LOYALTY_DISCOUNT_APPLIED) violated.

		

Even with the assertion in a novalidate state, the upgrade for customer status violates the assertion. Because the assertion involves a relationship between orders and customer status, changes in status are validated against existing order records. This is a bit different from traditional constraints, where novalidate can be used to ignore existing data that doesn’t satisfy the constraint.

The only way to update customer status to a new loyalty level is to disable the assertion. After updating customer status for Nina, we can reenable the constraint, but it should be left in a novalidate state.

			
alter assertion loyalty_discount_applied disable novalidate;
Assertion LOYALTY_DISCOUNT_APPLIED altered.
begin
    dbms_output.put_line('#Upgrade Nina to Preferred status with assertion disabled');
    update customers
    set status = 'Preferred' 
    where customer_name = 'Nina';
    commit;
end;
/
#Upgrade Nina to Preferred status with assertion disabled
alter assertion loyalty_discount_applied enable novalidate;
Assertion LOYALTY_DISCOUNT_APPLIED altered.

		

Test placing valid and invalid orders for the Nina’s new “Preferred” loyalty status:

			
begin
    dbms_output.put_line('#Place valid orders: Nina, Preferred, [0.05,0.05]');
    insert into orders(customer_name, discount)
    values('Nina', 0.05),('Nina', 0.05);
    
    commit;
end;
/
#Place valid orders: Nina, Preferred, [0.05,0.05]
begin
    dbms_output.put_line('#Place invalid order: Nina, Preferred, 0.01');
    insert into orders(customer_name, discount)
    values('Nina', 0.01);
exception
    when others then
        rollback;
        dbms_output.put_line(sqlerrm);
end;
/
#Place invalid order: Nina, Preferred, 0.01
ORA-08601: SQL assertion (PRACTICALPLSQL.LOYALTY_DISCOUNT_APPLIED) violated.

		

The assertion is working for new orders which are validated correctly at the current status level.

The problem with upgrading the customer status can only be resolved by disabling the assertion. To upgrade Nina’s status again, we have to disable the assertion again:

			
begin
    dbms_output.put_line('disable the assertion');
    execute immediate 'alter assertion loyalty_discount_applied disable novalidate';
    
    dbms_output.put_line('#Upgrade Nina to Elite status with assertion disabled');
    update customers
    set status = 'Elite' 
    where customer_name = 'Nina';
    commit;
    
    dbms_output.put_line('enable the assertion after upgrading customer status');
    execute immediate 'alter assertion loyalty_discount_applied enable novalidate';    
end;
/
disable the assertion
#Upgrade Nina to Elite status with assertion disabled
enable the assertion after upgrading customer status

		

Further testing at this new status of Elite shows that the assertion is working correctly for new data:

			
begin
   dbms_output.put_line('#Place valid orders: Nina, Elite, [0.10,0.11]');
    insert into orders(customer_name, discount)
    values('Nina', 0.10),('Nina', 0.11);
    
    commit;
end;
/
#Place valid orders: Nina, Elite, [0.10,0.11]
begin
    dbms_output.put_line('#Place invalid order: Nina, Elite, 0.05');
    insert into orders(customer_name, discount)
    values('Nina', 0.05);
exception
    when others then
        rollback;
        dbms_output.put_line(sqlerrm);
end;
/
#Place invalid order: Nina, Elite, 0.05
ORA-08601: SQL assertion (PRACTICALPLSQL.LOYALTY_DISCOUNT_APPLIED) violated.

		

The assertion is enabled in novalidate state and working correctly. We cannot enable the assertion in a validate state because existing data violates the assertion:

			
begin
    dbms_output.put_line('#enable the assertion in validate state');
    execute immediate 'alter assertion loyalty_discount_applied enable validate';
exception
    when others then
        dbms_output.put_line(sqlerrm);
end;
/
#enable the assertion in validate state
ORA-08601: SQL assertion (PRACTICALPLSQL.LOYALTY_DISCOUNT_APPLIED) violated.

		

Testing Updates To Loyalty Discount Minimums

What about changing the minimum discounts for the loyalty levels? This would cause the assertion to be revalidated for existing orders, and the updates to minimum discounts would fail:

			
begin
    dbms_output.put_line('#Change Preferred to 0.0625 minimum discount');
    update loyalty set discount_min = 0.0625
    where status = 'Preferred';
exception
    when others then
        rollback;
        dbms_output.put_line(sqlerrm);
end;
/
#Change Preferred to 0.0625 minimum discount
ORA-08601: SQL assertion (PRACTICALPLSQL.LOYALTY_DISCOUNT_APPLIED) violated.
begin
    dbms_output.put_line('#Change Elite to 0.1125 minimum discount');        
    update loyalty set discount_min = 0.1125
    where status = 'Elite';
exception
    when others then
        rollback;
        dbms_output.put_line(sqlerrm);
end;
/
#Change Elite to 0.1125 minimum discount
ORA-08601: SQL assertion (PRACTICALPLSQL.LOYALTY_DISCOUNT_APPLIED) violated.

		

We have to disable the assertion in order to update discount minimums:

			
begin
    dbms_output.put_line('disable the assertion to change loyalty discount minimum');
    execute immediate 'alter assertion loyalty_discount_applied disable novalidate';
    
    dbms_output.put_line('#Change Preferred to 0.0625 minimum discount');
    update loyalty set discount_min = 0.0625
    where status = 'Preferred';
    
    dbms_output.put_line('#Change Elite to 0.1125 minimum discount');        
    update loyalty set discount_min = 0.1125
    where status = 'Elite';
    
    commit;
    dbms_output.put_line('enable the assertion');
    execute immediate 'alter assertion loyalty_discount_applied enable novalidate';
end;
/
disable the assertion to change loyalty discount minimum
#Change Preferred to 0.0625 minimum discount
#Change Elite to 0.1125 minimum discount
enable the assertion

		

Testing with some orders shows that the new discount minimums are being validated by the assertion:

			
begin
    dbms_output.put_line('#Place valid orders: Prue, Preferred [0.07, 0.065]');
    insert into orders(customer_name, discount)
    values('Prue', 0.07), ('Prue', 0.065);
    dbms_output.put_line('#Place valid order: Nina, Elite, 0.12');
    insert into orders(customer_name, discount)
    values('Nina', 0.12);
    commit;
end;
/
#Place valid orders: Prue, Preferred [0.07, 0.065]
#Place valid order: Nina, Elite, 0.12
begin
    dbms_output.put_line('#Place invalid order: Prue, Preferred, 0.05');
    insert into orders(customer_name, discount)
    values('Prue', 0.05);
exception
    when others then
        rollback;
        dbms_output.put_line(sqlerrm);
end;
/
#Place invalid order: Prue, Preferred, 0.05
ORA-08601: SQL assertion (PRACTICALPLSQL.LOYALTY_DISCOUNT_APPLIED) violated.
begin
    dbms_output.put_line('#Place invalid order: Nina, Elite, 0.10');      
    insert into orders(customer_name, discount)
    values('Nina', 0.10);
exception
    when others then
        rollback;
        dbms_output.put_line(sqlerrm);
end;
/
#Place invalid order: Nina, Elite, 0.10
ORA-08601: SQL assertion (PRACTICALPLSQL.LOYALTY_DISCOUNT_APPLIED) violated.

		

Reviewing Successful Orders

The fact that the assertion can never be validated against existing orders and customers and loyalty minimum discounts is not very good news. Looking at all orders that were successfully placed during testing only uses the latest values for customer status and loyalty discount minimums. Because of this, most of the orders now show as having insufficient discounts, even though they were validated when originally placed:

			
select * from review_order_discounts
/
CUSTOMER_N STATUS     LOYALTY_DISCOUNT     ORDER_ID ORDER_DISCOUNT     DISCOUNT_VALID 
---------- ---------- ------------------ ---------- ------------------ ---------------
Liza       Elite      11.25%                      4 10%                Insufficient   
Liza       Elite      11.25%                      5 11%                Insufficient   
Nina       Elite      11.25%                      1 0%                 Insufficient   
Nina       Elite      11.25%                      2 5%                 Insufficient   
Nina       Elite      11.25%                      8 5%                 Insufficient   
Nina       Elite      11.25%                      9 5%                 Insufficient   
Nina       Elite      11.25%                     11 10%                Insufficient   
Nina       Elite      11.25%                     12 11%                Insufficient   
Nina       Elite      11.25%                     16 12%                Exceeds Minimum
Prue       Preferred  6.25%                       3 5%                 Insufficient   
Prue       Preferred  6.25%                      14 7%                 Exceeds Minimum
Prue       Preferred  6.25%                      15 6.5%               Exceeds Minimum

		

This does not look like a good report to show that the customer loyalty minimum discounts are being applied properly!

Summary

At a basic level, using the assertion to validate order discounts against customer loyalty status minimum requirements works, but only if customer status and loyalty discount level remain fixed. The proposed implementation of the customer loyalty program using assertions seems to be fatally flawed. Because customer status and/or loyalty discounts can change over time, the assertion cannot be enabled during these updates. A data validity rule that needs to be disabled while some transactional data is being updated is not a very good approach to data integrity.

Even if it was acceptible to disable the assertion during certain updates, the fact that the assertion can never be enabled in a validate state does not build confidence in the fact that the assertion is maintaining data integrity.

In part 2 of this series, we will look at a simplistic alternative which addresses both of these issues, allowing the assertion to be enabled in a validate state for orders, changes to customer status and changes to discount minimums.

TLDR;

The examples in this post can be executed completely using sql only or plsql for all data changes. For the plsql approach there is a package called sales_api with methods to place orders, update customer status and update loyalty discounts. The plsql approach allows for more elegant testing with succinct summaries of each dml action. Future posts in this series will leverage the plsql approach with additional examples of the sql approach in the repository.

run-example-1-plsql.sql (see also run-example-1-sql.sql for sql only approach)

			
spool run-example-1-plsql-results.txt
prompt running example with plsql api
@example-1-01-tables.sql
@example-1-02-assertion.sql
@example-1-03-view.sql
@example-1-04-package.sql
@example-1-05-tests-plsql.sql
@example-1-06-drop-all.sql
spool off

		

run-example-1-plsql-results.txt (see also run-example-1-sql-results.txt)

			
running example with plsql api
Table LOYALTY created.
insert the discount minimum for each loyalty status
PL/SQL procedure successfully completed.
Table CUSTOMERS created.
Table ORDERS created.
Assertion LOYALTY_DISCOUNT_APPLIED created.
View REVIEW_ORDER_DISCOUNTS created.
Package SALES_API compiled
Package Body SALES_API compiled
#Create customer Nina, status New
create customer Nina with status New COMMITTED
#Place valid orders: Nina, New, [0, 0.05]
place order for customer Nina with 0% discount COMMITTED
place order for customer Nina with 5% discount COMMITTED
#Create customer Prue, status Preferred
create customer Prue with status Preferred COMMITTED
#Place valid order: Prue, Preferred, 0.05
place order for customer Prue with 5% discount COMMITTED
#Create customer Liza, Elite
create customer Liza with status Elite COMMITTED
#Place valid orders: Liza, Elite, [0.10,0.11]
place order for customer Liza with 10% discount COMMITTED
place order for customer Liza with 11% discount COMMITTED
#Place invalid order: Prue, Preferred, 0
place order for customer Prue with 0% discount
    ROLLED BACK ORA-08601: SQL assertion (PRACTICALPLSQL.LOYALTY_DISCOUNT_APPLIED) violated.
#Place invalid order: Liza, Elite, 0.05
place order for customer Liza with 5% discount
    ROLLED BACK ORA-08601: SQL assertion (PRACTICALPLSQL.LOYALTY_DISCOUNT_APPLIED) violated.
PL/SQL procedure successfully completed.
all valid orders have discount minimum that matches customer status
CUSTOMER_N STATUS     LOYALTY_DISCOUNT     ORDER_ID ORDER_DISCOUNT     DISCOUNT_VALID 
---------- ---------- ------------------ ---------- ------------------ ---------------
Liza       Elite      10%                         4 10%                Meets Minimum  
Liza       Elite      10%                         5 11%                Exceeds Minimum
Nina       New        0%                          1 0%                 Meets Minimum  
Nina       New        0%                          2 5%                 Exceeds Minimum
Prue       Preferred  5%                          3 5%                 Meets Minimum  
upgrading Nina to preferred customer status fails because all order discounts are validated at the new status level
#Upgrade Nina to Preferred status with assertion enabled
update customer Nina status to Preferred
    ROLLED BACK ORA-08601: SQL assertion (PRACTICALPLSQL.LOYALTY_DISCOUNT_APPLIED) violated.
PL/SQL procedure successfully completed.
even setting the assertion to novalidate before updating status fails
the change in status still gets validated against all orders
Assertion LOYALTY_DISCOUNT_APPLIED altered.
#Upgrade Nina to Preferred status with assertion enabled novalidate
update customer Nina status to Preferred
    ROLLED BACK ORA-08601: SQL assertion (PRACTICALPLSQL.LOYALTY_DISCOUNT_APPLIED) violated.
PL/SQL procedure successfully completed.
disable the assertion to update the status
Assertion LOYALTY_DISCOUNT_APPLIED altered.
#Upgrade Nina to Preferred status with assertion disabled
update customer Nina status to Preferred COMMITTED
PL/SQL procedure successfully completed.
after updating the status, the assertion can only be enabled in novalidate state
Assertion LOYALTY_DISCOUNT_APPLIED altered.
the assertion is working for new orders only
#Place valid orders: Nina, Preferred, [0.05,0.05]
place order for customer Nina with 5% discount COMMITTED
place order for customer Nina with 5% discount COMMITTED
#Place invalid order: Nina, Preferred, 0.01
place order for customer Nina with 1% discount
    ROLLED BACK ORA-08601: SQL assertion (PRACTICALPLSQL.LOYALTY_DISCOUNT_APPLIED) violated.
PL/SQL procedure successfully completed.
Ninas original orders before the status update cannot be validated by the assertion
Ninas new orders are validated by the assertion
CUSTOMER_N STATUS     LOYALTY_DISCOUNT     ORDER_ID ORDER_DISCOUNT     DISCOUNT_VALID 
---------- ---------- ------------------ ---------- ------------------ ---------------
Liza       Elite      10%                         4 10%                Meets Minimum  
Liza       Elite      10%                         5 11%                Exceeds Minimum
Nina       Preferred  5%                          1 0%                 Insufficient   
Nina       Preferred  5%                          2 5%                 Meets Minimum  
Nina       Preferred  5%                          8 5%                 Meets Minimum  
Nina       Preferred  5%                          9 5%                 Meets Minimum  
Prue       Preferred  5%                          3 5%                 Meets Minimum  
7 rows selected. 
the only way to change customer status is to disable the assertion
Assertion LOYALTY_DISCOUNT_APPLIED altered.
#Upgrade Nina to Elite status with assertion disabled
update customer Nina status to Elite COMMITTED
PL/SQL procedure successfully completed.
enable the assertion after the status update in novalidate state
Assertion LOYALTY_DISCOUNT_APPLIED altered.
#Place valid orders: Nina, Elite, [0.10,0.11]
place order for customer Nina with 10% discount COMMITTED
place order for customer Nina with 11% discount COMMITTED
#Place invalid order: Nina, Elite, 0.05
place order for customer Nina with 5% discount
    ROLLED BACK ORA-08601: SQL assertion (PRACTICALPLSQL.LOYALTY_DISCOUNT_APPLIED) violated.
PL/SQL procedure successfully completed.
Nina's status is updated to elite, and all earlier orders show invalid discounts that cannot be validated by the assertion
CUSTOMER_N STATUS     LOYALTY_DISCOUNT     ORDER_ID ORDER_DISCOUNT     DISCOUNT_VALID 
---------- ---------- ------------------ ---------- ------------------ ---------------
Liza       Elite      10%                         4 10%                Meets Minimum  
Liza       Elite      10%                         5 11%                Exceeds Minimum
Nina       Elite      10%                         1 0%                 Insufficient   
Nina       Elite      10%                         2 5%                 Insufficient   
Nina       Elite      10%                         8 5%                 Insufficient   
Nina       Elite      10%                         9 5%                 Insufficient   
Nina       Elite      10%                        11 10%                Meets Minimum  
Nina       Elite      10%                        12 11%                Exceeds Minimum
Prue       Preferred  5%                          3 5%                 Meets Minimum  
9 rows selected. 
because existing data violates the assertion, it still cannot be validated
#enable the assertion in validate state
ORA-08601: SQL assertion (PRACTICALPLSQL.LOYALTY_DISCOUNT_APPLIED) violated.
PL/SQL procedure successfully completed.
changing loyalty discounts cannot be done with assertion enabled, existing data violates the assertion
#Change Preferred to 0.0625 minimum discount
update status Preferred to discount minimum 6.25%
    ROLLED BACK ORA-08601: SQL assertion (PRACTICALPLSQL.LOYALTY_DISCOUNT_APPLIED) violated.
#Change Elite to 0.1125 minimum discount
update status Elite to discount minimum 11.25%
    ROLLED BACK ORA-08601: SQL assertion (PRACTICALPLSQL.LOYALTY_DISCOUNT_APPLIED) violated.
PL/SQL procedure successfully completed.
to change the discount minimums, the assertion must be disabled
Assertion LOYALTY_DISCOUNT_APPLIED altered.
#Change Preferred to 0.0625 minimum discount
update status Preferred to discount minimum 6.25% COMMITTED
#Change Elite to 0.1125 minimum discount
update status Elite to discount minimum 11.25% COMMITTED
PL/SQL procedure successfully completed.
after updating the discounts, the assertion can be enabled in novalidate state
Assertion LOYALTY_DISCOUNT_APPLIED altered.
#Place valid orders: Prue, Preferred [0.07, 0.065]
place order for customer Prue with 7% discount COMMITTED
place order for customer Prue with 6.5% discount COMMITTED
#Place valid order: Nina, Elite, 0.12
place order for customer Nina with 12% discount COMMITTED
#Place invalid order: Prue, Preferred, 0.05
place order for customer Prue with 5% discount
    ROLLED BACK ORA-08601: SQL assertion (PRACTICALPLSQL.LOYALTY_DISCOUNT_APPLIED) violated.
#Place invalid order: Nina, Elite, 0.10
place order for customer Nina with 10% discount
    ROLLED BACK ORA-08601: SQL assertion (PRACTICALPLSQL.LOYALTY_DISCOUNT_APPLIED) violated.
PL/SQL procedure successfully completed.
while new orders will be validated by the assertion, not all existing orders meet the revised minimum discount requirements
CUSTOMER_N STATUS     LOYALTY_DISCOUNT     ORDER_ID ORDER_DISCOUNT     DISCOUNT_VALID 
---------- ---------- ------------------ ---------- ------------------ ---------------
Liza       Elite      11.25%                      4 10%                Insufficient   
Liza       Elite      11.25%                      5 11%                Insufficient   
Nina       Elite      11.25%                      1 0%                 Insufficient   
Nina       Elite      11.25%                      2 5%                 Insufficient   
Nina       Elite      11.25%                      8 5%                 Insufficient   
Nina       Elite      11.25%                      9 5%                 Insufficient   
Nina       Elite      11.25%                     11 10%                Insufficient   
Nina       Elite      11.25%                     12 11%                Insufficient   
Nina       Elite      11.25%                     16 12%                Exceeds Minimum
Prue       Preferred  6.25%                       3 5%                 Insufficient   
Prue       Preferred  6.25%                      14 7%                 Exceeds Minimum
Prue       Preferred  6.25%                      15 6.5%               Exceeds Minimum
12 rows selected. 
drop all objects
View REVIEW_ORDER_DISCOUNTS dropped.
Package SALES_API dropped.
Assertion LOYALTY_DISCOUNT_APPLIED dropped.
Table ORDERS dropped.
Table CUSTOMERS dropped.
Table LOYALTY dropped.

		

–Anthony Harper

Recent

Categories

Archives

2026 (3)
2025 (8)
2024 (5)
2023 (7)

Source code for this article can be found at https://github.com/gaiansentience/practicalplsql

Posted

March 15, 2026

SQL

Anthony Harper

Oracle AI Database 26.1, Sql Assertions

Discussion and Comments

4 responses to “Temporal SQL Assertions I: Unconditional and Unvalidated”

iudithd5bf8e4d8d

March 15, 2026

Hi Anthony,
Very insightful post, as always from you 🙂

I think this poses a “philosophical” problem regarding assertions.

There used always to be a conceptual distinction made between a CONSTRAINT and a TRIGGER, namely:
a. a CONSTRAINT restricts what the data should LOOK LIKE always ( well … regardless of the NOVALIDATE feature )
b. a TRIGGER, instead, restricts what you can do with the data, and, as such, it can have point in time dependencies, a.s.o.

In such a context, and in light of your examples, an ASSERTION looks more like a TRIGGER than like a CONSTRAINT.

In fact, the VALIDATE/NOVALIDATE applied to an assertion does NOT look like a completely accurate feature.
For a regular constraint, there is a clear delimiter of “existing” vs “new/changed” data, these being relative to a specific point in time, when the constraint was created/altered.

But, for an ASSERTION, such a delimitation is not possible, due to the mere essence of a multi-table ASSERTION.
So, even when it seems to work after an ENABLE NOVALIDATE, it only works due to the way it is implemented internally. What I mean by this is the fact that assertions were implemented technically by performing all kinds of optimizations to reduce the volume of the checks effectively performed during DML.
However, these optimizations are supposed NOT to impact in any way the correctness of the assertion outcome.

To put this otherwise, if such optimizations were not in place, then, in the case of your examples,
after making the assertion ENABLE NOVALIDATE, you would probably still not been able to place a correct ORDER (using the current CUSTOMER / LOYALTY data), because the other (old) orders do not all satisfy the assertion.
But, due to the optimizations, only the current order acted upon is being checked, and not all the others.

This was however not the case for an update of CUSTOMER or LOYALTY, where ENABLE NOVALIDATE was not sufficient (aka, the optimization has to cover a “wider range” of checks, involving all existing orders).

I guess that the solution in the following part(s) of this post will implement some form of TIME validity
for the tables and the assertion, which in fact covers the meaning of the real business case.

Cheers & Best Regards,
Iudith

LikeLike

Reply
Temporal SQL Assertions II: Ignore Legacy Data – Practical Pl/Sql

March 15, 2026

[…] the previous post (Temporal SQL Assertions I: Unconditional and Unvalidated) we explored a naive solution that used SQL assertions to validate customer discounts for a […]

LikeLike

Reply
Connor McDonald

March 15, 2026

Not near a PC so can’t immediately verify, but with regard to “After some experimentation I found that the assertion join has to use columns that are not nullable“, I think this can be relaxed to a non null check WITHIN the assertion (ie, where …. and status is not null )

LikeLike

Reply
1. Anthony Harper
  
  March 16, 2026
  
  Connor,
  Thanks for the suggestion. I tested this and it works, the assertion can be created when customer status is nullable if the assertion also checks that the status is not nul:
  
  example-1-02-assertion-alternate.sql
  prompt creating assertion works if null check is added to the assertion create assertion if not exists loyalty_discount_applied check ( not exists ( select 'order discount invalid for customer loyalty status' from orders o, customers c, loyalty s where o.customer_name = c.customer_name and c.status is not null and c.status = s.status and o.discount < s.discount_min ) ) /
  Assertion LOYALTY_DISCOUNT_APPLIED created.
  
  –Anthony
  
  LikeLike
  
  Reply