Using Assertions to enforce Business Rules

In Oracle, we have long been accustomed to defining constraints at the table level in the database. These are enforced for us by the database. Until recently, this was not possible for constraints between different tables, and we had to resort to triggers. In Oracle 26ai, assertions are introduced, to make constraints possible between tables. In this blog post I will show some examples of how assertions can be made of use.

The use case

Our use case is the CRM software of an energy supplier. The energy supplier supplies gas and/or electricity to its customers. So first we have a customers table:

create table customers 
( id   number generated by default on null as identity primary key
, name varchar2(400) not null
);

A customer can have have one or more locations, where energy should be delivered. Also the customer needs a location where the invoice can be sent, so there are two types of locations. We create a table like this:

create table locations 
( id            number generated by default as identity primary key
, ctr_id        number not null
, address       varchar2(400 char)
, location_type varchar2(15 char) not null
, constraint lcn_ctr_fk foreign key (ctr_id) references customers(id)
, constraint lcn_ck1    check (location_type in ('INVOICE','DELIVERY'))
);
create index lcn_ctr_fk on locations(ctr_id);

And each location of type 'Delivery' has one or more connections, either gas or electricity or both. The energy is supplied to these connections. The table will be something like:

create table connections 
( id              number generated by default as identity primary key
, lcn_id          number not null
, connection_type varchar2(15 char) not null
, constraint cnn_lcn_fk foreign key (lcn_id) references locations(id)
, constraint cnn_ck1    check (connection_type in ('GAS','ELECTRICITY'))
);
create index cnn_lcn_fk on connections(lcn_id);

Business Rule 1

The first Business Rule that we have is that a customer can only have one invoicing address. We could implement that using a trigger on the locations table, but it is hard to do it consistently.

The steps to take in defining a trigger to implement this business rule

We could define a trigger on the locations table. It would have to trigger on insert, but also on update of CTR_ID or LOCATION_TYPE. It would count the total number of locations belonging to the corresponding customer, and raise an exception if it is larger than one. A standard trigger is evaluated at statement level, but that would not be sufficient, for if two separate sessions both insert a new invoice location at the same time, and then both commit, two invoice locations will be present. So the trigger evaluation would have to be deferred to commit time. Also we would have to check the state of the database, because implementing the trigger does not correct any previous violations of the business rule.

With assertions, new in Oracle Database 26ai (23.26.1 to be specific), an elegant method is added to our toolbox to implement this business rule. An example of how this first Business Rule could be implemented is:

create assertion ctr_ck_one_invoice
check ( not exists (select 1 
                    from   locations lcn1 
                    where  lcn1.location_type = 'INVOICE' 
                    and    exists (select 1 
                                   from   locations lcn2 
                                   where  lcn2.location_type = 'INVOICE' 
                                   and    lcn2.ctr_id = lcn1.ctr_id 
                                   and    lcn2.id != lcn1.id
                                  )
                   )
      );

Of course, the first query that occurred to me to implement this Business Rule was a count of the locations, grouped by the customer ID. Unfortunately, group by and aggregate functions are not (yet?) supported by assertions, alongside a long list of other SQL features. The full list can be found in the documentation.

So with a bit of creativity, we can quickly come up with the query in the example. What it states is that we don't want to have (not exists) a location of type Invoice, that also haves (exists) a location of type Invoice belonging to the same customer with a different ID (in other words, a second Invoice location).

The beauty of the assertion, besides that it will enforce our Business Rule in the future, is that it also checks that the Business Rule is not violated in the current state of the database. If so, the create assertion statement will fail.

Now it succeeds, also because we have no rows yet. So we are going to insert some rows in the customers table:

insert into customers(name) values ('A. van der Put');
insert into customers(name) values ('Open-i');
commit;

Then we try to insert two Invoice locations for the first customer:

SQL> insert into locations(ctr_id,address,location_type) values (1,'Culemborg','INVOICE');

1 rij is gemaakt.

SQL> insert into locations(ctr_id,address,location_type) values (1,'Utrecht','INVOICE');
insert into locations(ctr_id,address,location_type) values (1,'Utrecht','INVOICE')
            *
FOUT in regel 1:
.ORA-08601: SQL-assertie (BLOG.CTR_CK_ONE_INVOICE) geschonden.
Help: https://docs.oracle.com/error-help/db/ora-08601/

As we expect, the insert of the second row fails.

Business Rule 2

Another Business Rule that we have in our CRM system, is that a location of type Delivery always should have at least one connection, either Gas or Electricity. Both is of course also possible. It would not make sense to have a delivery location where no energy can be delivered. The query for this assertion is straightforward: there shouldn't be (not exists) a location of type Delivery that doesn't have (not exists) a connection. In this case the assertion can not be checked at the statement level, because then it wouldn't be possible to do an insert of a location; there won't be any connections yet. So the assertion should be deferred to the end of the transaction, at commit time. The statement will then be:

create assertion lcn_ck_connection
check ( not exists ( select 1 
                     from   locations lcn
                     where  lcn.location_type = 'DELIVERY' 
                     and    not exists ( select 1 
                                         from   connections cnn
                                         where  cnn.lcn_id = lcn.id
                                       )
                   )
      ) deferrable initially deferred;

Now inserting a row into the locations table will succeed, but if we try to commit before a connection is inserted then the transaction will fail:

SQL> insert into locations(ctr_id,address,location_type) values (1,'Culemborg','DELIVERY');

1 rij is gemaakt.

SQL> commit;
commit
*
FOUT in regel 1:
.ORA-02091: Transactie is teruggedraaid.
ORA-08601: SQL-assertie (BLOG.LCN_CK_CONNECTION) geschonden.
Help: https://docs.oracle.com/error-help/db/ora-02091/

Final remarks

Assertions are a really valuable addition to all the great tools we already have in the Oracle database. I had fun playing around with them, but it can sometimes be frustrating, if you discover that a great SQL feature does not work with assertions. Also I encountered some cryptic error messages, like

ORA-08689: CREATE ASSERTION is mislukt. ORA-08673: Aangetroffen equijoin "LCN2"."CTR_ID"="LCN1"."CTR_ID" voldoet niet aan de criteria voor een FAST-validatie.

After some trying and ggogling I finally found out that I had forgotten the NOT NULL constraint on the CTR_ID column in the Locations table. After adding the constraint the assertion was created without problem.