Declarative Implementation of Mandatory Master-Detail relationship (Order must have Order Lines)

Since at least 1996, I have been involved with specifying and implementing business rules. Whenever possible, business rules are implemented in the database using declarative constraints. Triggers are the next step when the Unique, Foreign Key and Check Constraints are not sufficient. However, without very precise locking and serialization, trigger-based integrity enforcement almost always if not fool-proof. Having laid the foundation for CDM RuleFrame, I am somewhat ashamed to admit that while RuleFrame offers the best framework in town for complementing declarative database constraints with more complex trigger-based rule enforcement, it is not watertight. I have been looking for ways to stay declarative: more robust, more elegant and usually much faster than any other kind of implementation.

Tom Kyte’s seminar AskTom Live gave my colleague Andre and myself a clue I had been looking for. Declarative enforcement can be interpreted in more ways than the traditional table-based constraints. Here’s the main logic:

1. Materialized Views can aggregate data from base-tables
2. Declarative Constraints can be defined against Materialized Views
3. Materialized Views can be FAST REFRESHED ON COMMIT

How can we apply this to the (in)famous Mandatory Master Detail rule, that states that a Master cannot exist without at least one child and each Child needs to have a Master. A classic example is an ORDER master with an ORDER_LINE child. An ORDER without ORDER_LINES is meaningless and an ORDER_LINE can only exist in the context of an ORDER.

We use the following tables:

create table orders
( id number(4) primary key
, order_date date
)
/
create table order_lines
( id       number(10) primary key
, odr_id   number(4) not null
, seq      number(2)
, quantity number(6,2)
, product  varchar2(200)
)
/
alter table order_lines
add constraint ole_odr_fk foreign key (odr_id) references orders(id)
/

With the “not null” -ness of the odr_id column in ORDER_LINES and the foreign key on that same column to ORDERS.id, we enforce that each ORDER_LINE must refer to its parent. We can only create an ORDER_LINE if the ORDER it refers to already exists. That means we have implemented half of the business rule. However, using ordinary means, we cannot possibly enforce that an ORDER cannot be created unless it has ORDER_LINES. To examine our current situation, we create some ORDERS and ORDER_LINES:

insert into orders
(id, order_date)
values
(1 , sysdate -12)
/
insert into orders
(id, order_date)
values
(2 , sysdate -1)
/
insert into order_lines
( id, odr_id, seq, quantity, product)
values
( 1, 1, 1, 12, 'Toy Doll')
/
insert into order_lines
( id, odr_id, seq, quantity, product)
values
( 2, 1, 2, 3, 'Dinky Toy')
/
commit
/

Now it is time to see whether we are in compliance with the rule that specifies that ORDERS must always have ORDER_LINES (at least when we have committed):

select ole.odr_id ole_odr_id
,      odr.id odr_id
from   orders odr
,      order_lines ole
where  ole.odr_id(+) = odr.id
/
OLE_ODR_ID     ODR_ID
---------- ----------
         1          1
         1          1
                    2

This tells us that ORDER with ID value 2 has no order_lines. We could make it even more compact:

select odr.id "ORDER with no ORDER_LINES"
from   orders odr
,      order_lines ole
where  ole.odr_id(+) = odr.id
and    ole.rowid is null
/
ORDER with no ORDER_LINES
-------------------------
                        2

Clearly we have violated the business rule, as ORDER 2 is ORDER_LINES-less.

Materialized Views come to the rescue. We create a Materialized View, more or less based on the above query, that outer joins ORDERS with ORDER_LINES. This means that any ORDER is joined to all of its ORDER_LINES. If there are no ORDER_LINES for any given ORDER, it is outer-joined with an empty row. Note that MVs based on outer joins may not contain additional conditions in the WHERE-clause; therefore we need to use the first query rather than the even more compact second one.

create materialized view log on orders WITH SEQUENCE , PRIMARY KEY, ROWID (id, order_date) including new values ;
create materialized view log on order_lines with SEQUENCE , rowid (id, odr_id) including new values;
create materialized view odr_ole_mv
refresh force on commit
as
select ole.odr_id ole_odr_id, odr.id odr_id
from orders odr
,    order_lines ole
where ole.odr_id(+) = odr.id
/

Now once we have this Materialized View in place, it is easy to find the offending ORDERS:

select odr_id "Offending ORDER"
from   odr_ole_mv
where  ole_odr_id is null
/
Offending ORDER
---------------
              2

At this point, we will make use of the second consideration from the list above: CONSTRAINTS can be defined against Materialized Views. Since we have stated that an ORDER must have children, any record in the Materialized View odr_ole_mv where ole_odr_id is null (the result of an outer join without having ORDER_LINES to join the ORDER with) is in violation. We can prohibit these records using a Check Constraint:

alter table odr_ole_mv
add constraint "ORDER must have > 0 ORDER_LINE" check (ole_odr_id is not null)
/

Given the fact that currently we have an offending ORDER, we cannot enable the constraint. We first need to remove ORDER 2. When we have done that and we have enabled the Check Constraint, we can try to insert ORDER 2 again. The insert goes through. Only, when we commit, we get the following error:

SQL> commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (TEMP.ORDER must have > 0 ORDER_LINE) violated

Hence we now have implemented the Business Rule: ORDERS must have ORDER_LINES in addition to the reverse: an ORDER_LINE must have an ORDER.

Of course this opens up many possibilities for implementing complex business rules in a (semi-)declarative way. See the resources below for more background.

Resources

Data Warehousing: Room with a Better View, Oracle Magazine, March/April 2005 by Arup Nanda – an interesting introduction to Materialized Views in Oracle 10g – basic intro on Query Rewrite, the dbms_mview package and the related Hint – /*+ REWRITE_OR_ERROR */.

AskTom: Declarative IntegrityAs an advocate of enforcing as much integrity as possible declaratively, I am disappointed that little has changed in that arena in 10G. CHECK constraints are still limited to simple single-table, single-row checks, and triggers have to be used to enforce any more complex rules. Do you think Oracle will ever add multi-table check constraints or ASSERTIONS as defined by ANSI SQL? Or has declarative integrity gone about as far as it is going to in Oracle?

AskTom: Create Materialized View Log Semantics

Oracle 9iR2 Data Warehousing Guide – Types of Materialized Views – on which types of MVs (# Materialized Views with Aggregates, Materialized Views Containing Only Joins, Nested Materialized Views) we can discern and what requirements for each of these are. For example:

The SELECT clause in the materialized view creation statement defines the data that the materialized view is to contain. Only a few restrictions limit what can be specified. Any number of tables can be joined together. However, they cannot be remote tables if you wish to take advantage of query rewrite. Besides tables, other elements such as views, inline views (subqueries in the FROM clause of a SELECT statement), subqueries, and materialized views can all be joined or referenced in the SELECT clause. For fast refresh to be possible, the SELECT list must contain all of the GROUP BY columns (if present), and there must be a COUNT(*) and a COUNT(column) on any aggregated columns. Also, materialized view logs must be present on all tables referenced in the query that defines the materialized view. The valid aggregate functions are: SUM, COUNT(x), COUNT(*), AVG, VARIANCE, STDDEV, MIN, and MAX, and the expression to be aggregated can be any SQL value expression.

On Join-views:

Oracle does not allow self-joins in materialized join views. If you specify REFRESH FAST, Oracle performs further verification of the query definition to ensure that fast refresh can be performed if any of the detail tables change. These additional checks are:

* A materialized view log must be present for each detail table.
* The rowids of all the detail tables must appear in the SELECT list of the materialized view query definition.
* If there are no outer joins, you may have arbitrary selections and joins in the WHERE clause. However, if there are outer joins, the WHERE clause cannot have any selections. Further, if there are outer joins, all the joins must be connected by ANDs and must use the equality (=) operator.
* If there are outer joins, unique constraints must exist on the join columns of the inner table. For example, if you are joining the fact table and a dimension table and the join is an outer join with the fact table being the outer table, there must exist unique constraints on the join columns of the dimension table.

If some of these restrictions are not met, you can create the materialized view as REFRESH FORCE to take advantage of fast refresh when it is possible. If one of the tables did not meet all of the criteria, but the other tables did, the materialized view would still be fast refreshable with respect to the other tables for which all the criteria are met.

A materialized view log should contain the rowid of the master table. It is not necessary to add other columns.

To speed up refresh, you should create indexes on the materialized view’s columns that store the rowids of the fact table.