Two example constraints

In a post over at the Helsinki declaration blog, I've introduced two rather simple constraints in a two table database design holding a BOOK and ORDERLINE table.

• A user cannot have more than a (sum) total of fifty books in ORDERLINE.
• A title cannot be ordered more than a (sum) total of eighty in ORDERLINE.
  

As assertions we would specify these as follows:

create assertion no_more_than_50_per_user as
check(not exists(select 'a user with more than 50 books'
                 from (select username,sum(amount) as total
                       from orderline
                       group by username)
                 where total > 50)
);

create assertion no_more_than_80_per_title as
check(not exists(select 'a title with more than 80 ordered'
                 from (select ean,sum(amount) as total
                       from orderline
                       group by ean)
                 where total > 80)
);

Implementing these two constraints with the RuleGen framework is fairly straightforward. Let's start with the first one, no_more_than_50_per_user. What we first need to tell RuleGen is when this constraints needs to be validated:

• on inserts?
• on deletes?
• on updates?

Now obviously whenever an ORDERLINE row is deleted, there is no need to validate this constraint. When we insert an ORDERLINE the constraint must be validated, as the insert could introduce a total of more than fifty books for a user. Likewise when we update the AMOUNT column and increase its current value, the constraint must be validated too.

The way we tell RuleGen this is by specifying (in this case) two queries:

select username
from inserted_rows;

select new_username as username
from updated_rows
where new_amount > old_amount;

The objects selected from by these queries (inserted_rows and updated_rows) are maintained by the framework and will hold the rows inserted/updated by the current transaction. We select the username value in above queries to tell RuleGen that this constraint is to be validated per username. RuleGen will execute these queries, and upon rows fetched from them, continue with the next step.

Next we need to tell RuleGen how this constraint is to be validated. This is done by specifying yet another query. A query on the tables that are constrained. The goal of this query is to seek a violation of the constraint. Here it is.

select 'Cannot have more than fifty books ordered for user '||p_username||'. Found: '||TOTAL||'.' as MSG
from (select sum(AMOUNT) as TOTAL
      from ORDERLINE
      where USERNAME = p_username)
where TOTAL > 50;

When this query fetches a row, RuleGen will use the MSG-value to raise an exception, signalling that the constraint is violated.

The second constraint is very much like the one demonstrated above. I'll leave it up to the reader to specify the RuleGen when and how for that one.