Monday, August 25, 2008

Implementing dynamic data constraints

Previous post: Implementing inter-entity rules



Create rules

These rules either originate from a decomposed inter-entity rule or they were invented on their own. Again you have two choices of implementing them: using database triggers or incorporating in your api's. And again it's a matter of whether you are willing to lose performance or not. You'll probably want to be consistent throughout all business rule implementations. Stick with a database trigger approach or with an api approach. On the plus side for database triggers this time, some create rules can be validated using a before statement insert trigger instead of for each row. This means you won't have to pay a performance penalty for the occasional rule like "You may not create a project assignment when the OUT_OF_BUDGET system variable is set." Although most create rules require querying some related record and will suffer the row-by-row processing performance penalty. When using an api approach, you'll again code all create rules to be able to handle a set of records for maximum performance.


Attribute transition rules

This is a category where it is actually convenient to use a database trigger. It's because of the easy access to the :old and :new values in a row level trigger. The example "Allowed transitions for civil state of an employee are Unmarried -> Married -> Divorced -> Married." can be implemented like this:

rwijk@ORA11G> create table employees
2 ( name varchar2(10) primary key
3 , civil_state varchar2(1) check (civil_state in ('U','M','D'))
4 )
5 /

Table created.

rwijk@ORA11G> insert into employees
2 select 'ALAN', 'U' from dual union all
3 select 'BOB', 'M' from dual union all
4 select 'CLARK', 'D' from dual
5 /

3 rows created.

rwijk@ORA11G> create trigger employees_bru
2 before update of civil_state on employees for each row
3 begin
4 if :old.civil_state || :new.civil_state not in ('UM','MD','DM')
5 then
6 raise_application_error(-20000,'Illegal transition of civil state');
7 end if;
8 end;
9 /

Trigger created.

rwijk@ORA11G> update employees
2 set civil_state = 'D'
3 where name = 'BOB'
4 /

1 row updated.

rwijk@ORA11G> update employees
2 set civil_state = 'U'
3 where name = 'BOB'
4 /
update employees
*
ERROR at line 1:
ORA-20000: Illegal transition of civil state
ORA-06512: at "RWIJK.EMPLOYEES_BRU", line 4
ORA-04088: error during execution of trigger 'RWIJK.EMPLOYEES_BRU'


Using the api approach you will have api-procedures for "getting_married" or "getting_a_divorce" where the check will be in the where clause. An error message can then be raised when 0 rows are updated (if sql%rowcount = 0 then raise_application_error...).


Transferable relationship rules

This one is easy using an api approach: don't offer an api-procedure that transfers a relationship. Using a database trigger approach is also quite simple: "if updating('<fk-column>') then raise_application_error(...); end if;"


Other update rules, modify rules and other delete rules

The same holds true for these rules as described in the section "Create rules".


Relationship rules

Relationship rules were also mentioned as a static data constraint, and now they appear again as a dynamic data constraint. Strictly speaking it is right to split them into two categories, although it is a little fancy. The dynamic relationship rules are implemented by the keywords ON DELETE CASCADE and ON DELETE SET NULL in the foreign key constraint and by the foreign key constraint itself. The example "If a department that still has employees is deleted, give an error" is what happens if you define a regular foreign key constraint. The ON DELETE CASCADE will automatically delete child records when the master is deleted, instead of erroring out. And the ON DELETE SET NULL will automatically orphan child records when the master is deleted.

Next post: Implementing change event rules

Thursday, August 21, 2008

Implementing inter-entity rules

Previous post in this serie: Implementing entity rules.



Relationship rules

Relationship rules are implemented by foreign keys. This seems very straightforward, but there is a little more to it. You might have to add a not null constraint to the foreign key column to implement the optionality of the relationship. Or an extra unique key constraint on the foreign key column to implement a 1:1 relationship. And when you have allowed N:M relationships in your entity-relationship-diagram, you might even have to implement extra intersection tables.

Also, a relationship rule doesn't necessarily have to be an inter-entity rule, in case of a recursive relationship, when a row refers to a row of the same entity. Because the drawing in an entity relationship diagram of such a relationship when drawn from the top bar of the entity to the side bar, resembles a pig's ear, it's often called as such. The best known example of such a relationship rule is the "EMP.MGR -> EMP.EMPNO" relationship in the EMP-table. In this case it's really an entity rule. But this change of classification doesn't matter much, since no code is involved, the relationship rules are typically not mentioned separately.


Restricted relationship rules

The example in the picture above "An employee can only be managed by an employee with job 'MANAGER'" is a restricted relationship rule, but it isn't exactly "inter-entity". But in general, they are. And no matter if the referring entity is the same entity or another one, the implementation of these kind of rules has become quite easy. The trick is to have a fast refreshable materialized view with a join of both ends of the relationship and the relevant attributes. Once you have the rows joined together into one materialized view row, the implementation is as easy as creating one check constraint. An example:

rwijk@ORA11G> create table myemps as select * from emp where 1=0
2 /

Table created.

rwijk@ORA11G> create materialized view log on myemps with rowid
2 /

Materialized view log created.

rwijk@ORA11G> create materialized view myemps_and_their_managers
2 refresh fast on commit
3 as
4 select e.rowid erid
5 , m.rowid mrid
6 , m.job
7 from myemps e
8 , myemps m
9 where e.mgr = m.empno
10 /

Materialized view created.

rwijk@ORA11G> alter table myemps_and_their_managers
2 add constraint mgr_is_manager_ck
3 check (job = 'MANAGER')
4 /

Table altered.

rwijk@ORA11G> insert into myemps (empno,ename,job,mgr)
2 values (1,'ALAN','MANAGER',null)
3 /

1 row created.

rwijk@ORA11G> insert into myemps (empno,ename,job,mgr)
2 values (2,'BOB','CLERK',1)
3 /

1 row created.

rwijk@ORA11G> commit
2 /

Commit complete.

rwijk@ORA11G> insert into myemps (empno,ename,job,mgr)
2 values (3,'CLIFF','SALESMAN',2)
3 /

1 row created.

rwijk@ORA11G> commit
2 /
commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (RWIJK.MGR_IS_MANAGER_CK) violated


Other inter-entity rules

For most other inter-entity rules the check-constraint-on-a-fast-refreshable-materialized-view solution will work as well, since most inter-entity rules describe a condition that exists between various attributes of different entities. The materialized view can take care of putting the related data in a single row and then the check constraint is a piece of cake.

The example in the picture above "The project assignment start and end dates for an employee should lie between the start and end dates defined for the project" can therefore be implemented in exactly the same way as restricted relationship rules:

rwijk@ORA11G> create table projects
2 ( id number(9) primary key
3 , name varchar2(30) not null
4 , startdate date not null
5 , enddate date not null
6 )
7 /

Table created.

rwijk@ORA11G> create table project_assignments
2 ( id number(9) primary key
3 , employee varchar2(10) not null
4 , project_id number(9) not null references projects(id)
5 , startdate date not null
6 , enddate date not null
7 )
8 /

Table created.

rwijk@ORA11G> create materialized view log on projects with rowid
2 /

Materialized view log created.

rwijk@ORA11G> create materialized view log on project_assignments with rowid
2 /

Materialized view log created.

rwijk@ORA11G> create materialized view projects_date_ranges
2 refresh fast on commit
3 as
4 select p.rowid prid
5 , pa.rowid parid
6 , p.startdate project_startdate
7 , p.enddate project_enddate
8 , pa.startdate assignment_startdate
9 , pa.enddate assignment_enddate
10 from projects p
11 , project_assignments pa
12 where p.id = pa.project_id
13 /

Materialized view created.

rwijk@ORA11G> alter table projects_date_ranges
2 add constraint valid_assignment_periods_ck
3 check ( assignment_startdate >= project_startdate
4 and assignment_enddate <= project_enddate
5 )
6 /

Table altered.

rwijk@ORA11G> insert into projects
2 values (1,'MJPS',date '2008-01-01',date '2008-12-31')
3 /

1 row created.

rwijk@ORA11G> insert into project_assignments
2 values (1,'ALAN',1,date '2008-01-01',date '2008-06-30')
3 /

1 row created.

rwijk@ORA11G> commit
2 /

Commit complete.

rwijk@ORA11G> insert into project_assignments
2 values (2,'BOB',1,date '2008-01-01',date '2009-01-01')
3 /

1 row created.

rwijk@ORA11G> commit
2 /
commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (RWIJK.VALID_ASSIGNMENT_PERIODS_CK) violated


But the question is: can all other inter-entity rules be implemented using a materialized view? All the ones I can think of, can be, since the attributes are always somehow related and can be joined. I would be very interested to know if someone who is reading this knows an inter-entity rule that cannot be implemented using materialized views. Theoretically such rules must exist.

And when you come across such a rule, you would have to go back to the old method of decomposing an inter-entity rule into several dynamic data constraints, one for each triggering moment. And then handle each one separately. For example, let's just assume that the business rule "The project assignment start and end dates for an employee should lie between the start and end dates defined for the project" cannot be implemented using materialized views. The triggering moments are:
  • insert of a new project assignment
  • update of an existing project assignment start and/or end date
  • update of the relation between project and project assignment
  • update of a project's start and/or end date

If you choose database triggers to implement these decomposed rules, you'll have to accomodate for all four situations and write code for each situation. When using an api approach, you'll have to map these four triggering moments to your existing api functions and procedures and see which one of these triggering moments is actually possible. You can maybe skip a few implementations this way. And then don't forget to check which business rules to enforce when adding an extra api procedure. And make sure the resulting code, whether using the database trigger approach or the api approach, takes care of serializing access for a correct implementation.

Next post: Implementing dynamic data constraints

Monday, August 18, 2008

The trouble with triggers

The September/October 2008 issue of Oracle magazine is out. In this issue you'll find an article by Tom Kyte called The Trouble with Triggers. It ties in nicely with the current series in this blog about implementing business rules. And if I wasn't able to convince you that database triggers are evil, I'm sure that Tom will.

Monday, August 11, 2008

Implementing entity rules

Previous post in this serie: Implementing attribute and tuple rules



Attribute rules and tuple rules were fairly easy. The fun part starts with the entity rules and - in a next post - the inter-entity rules. According to the picture above, entity rules come in two flavours: unique identifier rules and other entity rules. However, since the other entity rules can be implemented in two clearly distinct ways, I will describe a separate subclass which I will call aggregation entity rules. But first things first:


Unique Identifier Rules

The implementation of rules belonging to this subclass is trivial at first glance: by a unique constraint and the accompanying unique index. However, not all unique identifier rules are simply bound to one column or a set of columns. But with the help of function based unique indexes you can implement the complexer ones as well. An example:

Business rule: A customer can only have one non-processed order

rwijk@ORA11G> create table orders
2 ( id number(9)
3 , customer_id number(9)
4 , processed_indicator varchar2(1)
5 , constraint inv_processed_domain_ck check (processed_indicator in ('Y','N'))
6 )
7 /

Tabel is aangemaakt.

rwijk@ORA11G> create unique index non_processed_orders_uk on orders
2 ( decode(processed_indicator,'N',customer_id)
3 )
4 /

Index is aangemaakt.

rwijk@ORA11G> insert into orders values (1, 101, 'Y')
2 /

1 rij is aangemaakt.

rwijk@ORA11G> insert into orders values (2, 101, 'Y')
2 /

1 rij is aangemaakt.

rwijk@ORA11G> insert into orders values (3, 101, 'N')
2 /

1 rij is aangemaakt.

rwijk@ORA11G> insert into orders values (4, 101, 'N')
2 /
insert into orders values (4, 101, 'N')
*
FOUT in regel 1:
.ORA-00001: Schending van UNIQUE-beperking (RWIJK.NON_PROCESSED_ORDERS_UK).


rwijk@ORA11G> insert into orders values (4, 101, 'Y')
2 /

1 rij is aangemaakt.


It is even possible to create a deterministic function and use it in a unique function based index. So this subclass can be implemented entirely declarative.


Aggregation rules

All former other entity rules that check for some aggregated value, will be called aggregation rules here. The "No more than 20 departments are allowed" example mentioned in the picture is an aggregation rule. These rules can be implemented by a check constraint on a on commit refreshable materialized view. This means they will be validated at the transaction level instead of at statement level. This is just perfect for these kind of rules, because for an aggregation rule to stay valid, you'll sometimes have to modify more than one row. Here is an example:

Business rule: The sum of all percentages per month for a customer has to be 100%.

rwijk@ORA11G> create table payment_percentages
2 ( customer_id number(9)
3 , month number(2,0)
4 , percentage number(3,0)
5 )
6 /

Tabel is aangemaakt.

rwijk@ORA11G> create materialized view log on payment_percentages
2 with rowid (customer_id,percentage) including new values
3 /

Snapshotlog is aangemaakt.

rwijk@ORA11G> create materialized view mv
2 refresh fast on commit
3 as
4 select customer_id
5 , sum(percentage) total_percentage
6 , count(*) count_all
7 , count(percentage) count_percentage
8 from payment_percentages
9 group by customer_id
10 /

Snapshot is aangemaakt.

rwijk@ORA11G> alter materialized view mv
2 add constraint mv_total_percentage_100_ck
3 check (total_percentage = 100)
4 /

Snapshot is gewijzigd.

rwijk@ORA11G> desc mv
Naam Null? Type
----------------------------------------- -------- ----------------------------
CUSTOMER_ID NUMBER(9)
TOTAL_PERCENTAGE NUMBER
COUNT_ALL NUMBER
COUNT_PERCENTAGE NUMBER

And that's all there is to it. Note that some extra aggregates were added to the materialized views. This is mandatory as can be read here in the documentation about restriction on fast refresh on materialized views with aggregates. The materialized view log will capture all changes, the materialized will be refreshed on commit time, and the check constraint will automatically validate the new data. Here is an example of how to deal with inserting and updating:

rwijk@ORA11G> insert into payment_percentages
2 select 1
3 , level
4 , 10
5 from dual
6 connect by level <= 8
7 /

8 rijen zijn aangemaakt.

rwijk@ORA11G> insert into payment_percentages
2 select 1
3 , 8+ level
4 , 5
5 from dual
6 connect by level <= 4
7 /

4 rijen zijn aangemaakt.

rwijk@ORA11G> commit
2 /

Commit is voltooid.


This commit is when the check constraint mv_total_percentage_100_ck on the materialized view gets validated. The sum of all percentages is calculated and it is 100%, so no error is raised.

rwijk@ORA11G> select customer_id
2 , month
3 , sum(percentage)
4 from payment_percentages
5 group by rollup ((customer_id,month))
6 order by month
7 /

CUSTOMER_ID MONTH SUM(PERCENTAGE)
----------- ---------- ---------------
1 1 10
1 2 10
1 3 10
1 4 10
1 5 10
1 6 10
1 7 10
1 8 10
1 9 5
1 10 5
1 11 5
1 12 5
100

13 rijen zijn geselecteerd.

rwijk@ORA11G> update payment_percentages
2 set percentage = 15
3 where customer_id = 1
4 and month = 11
5 /

1 rij is bijgewerkt.

rwijk@ORA11G> commit
2 /
commit
*
FOUT in regel 1:
.ORA-12008: Fout in pad voor vernieuwen van snapshot.
ORA-02290: CHECK-beperking (RWIJK.MV_TOTAL_PERCENTAGE_100_CK) is geschonden.


But now the sum is more than 100% and so the above error is raised. To successfully change a few values, several separate update statements are needed, like this:

rwijk@ORA11G> update payment_percentages
2 set percentage = 15
3 where customer_id = 1
4 and month = 11
5 /

1 rij is bijgewerkt.

rwijk@ORA11G> update payment_percentages
2 set percentage = 8
3 where customer_id = 1
4 and month = 2
5 /

1 rij is bijgewerkt.

rwijk@ORA11G> update payment_percentages
2 set percentage = 2
3 where customer_id = 1
4 and month = 5
5 /

1 rij is bijgewerkt.

rwijk@ORA11G> commit
2 /

Commit is voltooid.

rwijk@ORA11G> select customer_id
2 , month
3 , sum(percentage)
4 from payment_percentages
5 group by rollup ((customer_id,month))
6 order by month
7 /

CUSTOMER_ID MONTH SUM(PERCENTAGE)
----------- ---------- ---------------
1 1 10
1 2 8
1 3 10
1 4 10
1 5 2
1 6 10
1 7 10
1 8 10
1 9 5
1 10 5
1 11 15
1 12 5
100

13 rijen zijn geselecteerd.

And the sum is 100% again.


Other entity rules

Because of the split-off of the aggregation rules, this subclass has become a little smaller, but it still represents a good proportion of all business rules. The most known example of an other entity rule is an overlap check on several periods (startdate and enddate) belonging to a same group. This subclass is one we cannot solve declaratively, or at least it is not known by me. So we have to make a choice here: implement them using database triggers or implement them inside your api.

Business rule: The periods of one customer are not allowed to overlap.

Implementing other entity rules with database triggers

You can see an example of a period overlap check - amidst the implementation of two other entity rules - in this post about a test with RuleGen. Check for the "check_period_overlap" procedure. As you can see, the trickier parts are to serialize access by customer_id and circumventing the mutating table error. Trickier, but standard. The advantage of using database triggers to enforce your other entity rules, is that once it is in place, you are covered. And although I generally think that database triggers are evil, the static data constraint rules are the only place they can be acceptable if you are willing to lose performance.

Implementing other entity rules inside your api

If you are not willing to compromise performance then you should take this approach. In the previously mentioned post you'll also see an example of validating this rule using an api. It is implemented here as part of that one api procedure. But what to do when more than one api procedure can violate the business rule? You'll want to prevent double coding, but you'll also want it to be optimized for single row online inserts and updates, as well as in batch inserts and updates at night. An answer to this problem is to code a business rule validation procedure only once and have it accept a table of values/records by creating a SQL type. The SQL type will be the input parameter of the validation procedure. The single row update procedure will call it with a table with 1 value/record, and the batch update procedure will deliver many more records. An example:

rwijk@ORA11G> create table subscriptions
2 ( id number(9) primary key
3 , customer_id number(9) not null
4 , startdate date not null
5 , enddate date
6 , amount number(6)
7 , unique (customer_id,startdate)
8 )
9 /

Tabel is aangemaakt.

rwijk@ORA11G> insert /*+ append */ into subscriptions
2 select level
3 , ceil(level/2)
4 , case mod(level,2) when 0 then date '2006-01-01' else date '2007-01-01' end
5 , case mod(level,2) when 0 then date '2007-01-01' else date '2008-01-01' end
6 , 0
7 from dual
8 connect by level <= 10000
9 /

10000 rijen zijn aangemaakt.

rwijk@ORA11G> create type tt_customer_ids is table of number(9);
2 /

Type is aangemaakt.

rwijk@ORA11G> create package subscriptions_api
2 as
3 procedure single_row_update
4 ( p_id in number
5 , p_startdate in date
6 , p_enddate in date
7 );
8 procedure batch_update
9 ;
10 end subscriptions_api;
11 /

Package is aangemaakt.

rwijk@ORA11G> create package body subscriptions_api
2 as
3 procedure lock_customers(p_customer_ids tt_customer_ids)
4 is
5 l_lock_dummy integer;
6 begin
7 for i in 1 .. p_customer_ids.count
8 loop
9 l_lock_dummy := dbms_lock.request
10 ( release_on_commit => true
11 , id => dbms_utility.get_hash_value
12 ( name => 'contracts_pkg.lock_customer' || to_char(p_customer_ids(i))
13 , base => 1
14 , hash_size => power(2,30)
15 )
16 );
17 end loop;
18 end lock_customers
19 ;
20 function exists_overlap(p_customer_ids tt_customer_ids) return boolean
21 is
22 l_customer_id_dummy subscriptions.customer_id%type;
23 begin
24 select customer_id
25 into l_customer_id_dummy
26 from ( select s.customer_id
27 , sign
28 ( lead(s.startdate,1,date '9999-12-31')
29 over
30 (partition by s.customer_id order by s.startdate)
31 - nvl(s.enddate,date '9999-12-31')
32 ) sgn
33 from subscriptions s
34 where s.customer_id in
35 ( select /*+ cardinality (c 1) */
36 column_value
37 from table(p_customer_ids) c
38 )
39 )
40 where sgn = -1
41 ;
42 return true;
43 exception
44 when no_data_found then
45 return false;
46 when too_many_rows then
47 return true;
48 end exists_overlap
49 ;
50 procedure check_period_overlap (p_customer_ids tt_customer_ids)
51 is
52 begin
53 if exists_overlap(p_customer_ids)
54 then
55 raise_application_error
56 ( -20001
57 , 'Periods of subscription of one customer cannot overlap.'
58 );
59 end if;
60 end check_period_overlap
61 ;
62 procedure single_row_update
63 ( p_id in number
64 , p_startdate in date
65 , p_enddate in date
66 )
67 is
68 l_customer_ids tt_customer_ids;
69 begin
70 update subscriptions
71 set startdate = p_startdate
72 , enddate = p_enddate
73 where id = p_id
74 returning customer_id bulk collect into l_customer_ids
75 ;
76 lock_customers(l_customer_ids);
77 check_period_overlap(l_customer_ids)
78 ;
79 end single_row_update
80 ;
81 procedure batch_update
82 is
83 l_customer_ids tt_customer_ids;
84 begin
85 update subscriptions
86 set enddate = enddate + interval '1' year
87 where amount = 0
88 returning customer_id bulk collect into l_customer_ids
89 ;
90 lock_customers(l_customer_ids);
91 check_period_overlap(l_customer_ids)
92 ;
93 end batch_update
94 ;
95 end subscriptions_api;
96 /

Package-body is aangemaakt.

rwijk@ORA11G> begin
2 subscriptions_api.single_row_update
3 ( p_id => 1234
4 , p_startdate => date '2006-01-01'
5 , p_enddate => date '2008-01-01'
6 );
7 end;
8 /
begin
*
FOUT in regel 1:
.ORA-20001: Periods of subscription of one customer cannot overlap.
ORA-06512: in "RWIJK.SUBSCRIPTIONS_API", regel 55
ORA-06512: in "RWIJK.SUBSCRIPTIONS_API", regel 77
ORA-06512: in regel 2


rwijk@ORA11G> begin
2 subscriptions_api.batch_update;
3 end;
4 /
begin
*
FOUT in regel 1:
.ORA-20001: Periods of subscription of one customer cannot overlap.
ORA-06512: in "RWIJK.SUBSCRIPTIONS_API", regel 55
ORA-06512: in "RWIJK.SUBSCRIPTIONS_API", regel 91
ORA-06512: in regel 2

A few things to note:
  1. All customer records are validated in this implementation, which is good when only few records per customer are expected. When lots of records per customer are expected, this will be less feasible.

  2. The cardinality hint is used to not use the default blocksize cardinality (8K here), but 1. The resulting plan will therefore favour the use of an index on customer_id (and startdate).

  3. You can extend this example by using error logging and capturing all records that violate the business rule, and even report all customer_id's and periods in the error message.

  4. When implementing several business rules in your api, you will want to use only one SQL type, so the resulting type will have to accomodate for all columns needed in all business rules.

  5. I have not much experience with this method yet, but I will use and test it in the near future. If anything noteworthy will occur, it will be on this blog of course.


Next post: Implementing inter-entity rules

Thursday, August 7, 2008

La clause MODEL d'Oracle

Today, Antoine Dinimant (Antoun) has released a translation of my SQL Model Clause Tutorials, part one and part two, on the French developers' community site developpez.com.

The official title of the translated version is: "Intégrez un modèle de calcul à vos requêtes SQL : la clause MODEL d'Oracle" and can be seen here. The translated version looks very good and contains some extra notes by Antoun. You can even download the French version as a PDF.

The original tutorials on this blog now also contain a link to the French versions of Antoun.

Thanks Antoine, for a job well done.

Friday, August 1, 2008

Implementing attribute and tuple rules

Previous post: Implementing business rules

The first of the five main classes of business rules according to CDM RuleFrame's classification scheme is the class "Static Data Constraint Rules". Each rule describes a state the data must always adhere to. By having them in place - and having them properly implemented of course - your data integrity is ensured. Static data constraints have 4 subclasses: attribute rules, tuple rules, entity rules and inter-entity rules. This post will cover the attribute and tuple rules.

Attribute rules



Simple attribute rules

Simple attribute rules describe the datatype and their length. These rules are rarely described separately as a business rule. Instead they are incorporated in the entity-relationship-model in the attribute definition. Although Oracle extends the number of possible datatypes with each version, the way of implementing them has not changed over the years. Just specify a datatype and length for each attribute.

Domain rules

Domain rules are implemented by check constraints. When there are a lot of domain values, or when the domain values change a lot, you are better of modelling a separate entity. And then the business rule won't be an attribute domain rule, but an inter-entity relationship rule. Domains are just the static ones with a few values. An example:

rwijk@ORA11G> create table persons
2 ( id number(9)
3 , name varchar2(10)
4 , gender_code varchar2(1)
5 , constraint psn_gender_code_ck check (gender_code in ('F','M'))
6 )
7 /

Tabel is aangemaakt.

rwijk@ORA11G> insert into persons (id,name,gender_code)
2 values (1,'JOE','Z')
3 /
insert into persons (id,name,gender_code)
*
FOUT in regel 1:
.ORA-02290: CHECK-beperking (RWIJK.PSN_GENDER_CODE_CK) is geschonden.


Other attribute rules

All other static data constraints that are not simple attributes or domain rules, fall in this other category. Most of them can be implemented by check constraints. For example:

rwijk@ORA11G> create table persons
2 ( id number(9)
3 , name varchar2(10)
4 , salary number(6)
5 , constraint psn_salary_multiple_of_100_ck check (mod(salary,100)=0)
6 )
7 /

Tabel is aangemaakt.

rwijk@ORA11G> insert into persons (id,name,salary)
2 values (1,'JOE',1950)
3 /
insert into persons (id,name,salary)
*
FOUT in regel 1:
.ORA-02290: CHECK-beperking (RWIJK.PSN_SALARY_MULTIPLE_OF_100_CK) is geschonden.


However, there used to exist a few attribute rules that could not be validated using a check constraint. For example, in the Netherlands, each bank account number must satisfy the "eleven test" (dutch: elfproef). This algorithm is designed such that switching two digits from a bank account number or mistyping one digit always makes an account number invalid. The individual digits of a bank account number are multiplied by a factor that increases from 1 until 9 when read from right to left. The sum of them should be divisible by 11. If not, the bank account number is invalid. The function below implements this small algorithm:

rwijk@ORA11G> create function satisfies_the_eleven_test
2 ( p_bank_account_no in number
3 ) return varchar2 deterministic
4 is
5 l_account_no pls_integer := p_bank_account_no;
6 l_sum pls_integer := 0;
7 l_factor integer := 1;
8 begin
9 while l_account_no > 0
10 loop
11 l_sum := l_sum + mod(l_account_no,10) * l_factor;
12 l_factor := l_factor + 1;
13 l_account_no := trunc(l_account_no/10);
14 end loop
15 ;
16 return case mod(l_sum,11) when 0 then 'Y' else 'N' end
17 ;
18 end satisfies_the_eleven_test;
19 /

Functie is aangemaakt.


In earlier versions you would either:
- create a database trigger firing before each row insert or update calling the above function, or
- you would incorporate the function above into the api procedures and functions dealing with new or updated bank account numbers.

Note that in theory you could create some regular check constraint for this rule (think about it ...), but that would be a huge unmaintainable expression and one you cannot reuse. In Oracle11 we have virtual columns to implement them far more gracefully:

rwijk@ORA11G> create table persons
2 ( id number(9)
3 , name varchar2(10)
4 , bank_account_no number(10,0)
5 , account_satisfies_eleven_test as
6 (cast(satisfies_the_eleven_test(bank_account_no) as varchar2(1)))
7 , constraint psn_account_eleven_test_ck
8 check (account_satisfies_eleven_test = 'Y')
9 )
10 /

Tabel is aangemaakt.

rwijk@ORA11G> desc persons
Naam Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER(9)
NAME VARCHAR2(10)
BANK_ACCOUNT_NO NUMBER(10)
ACCOUNT_SATISFIES_ELEVEN_TEST VARCHAR2(1)

rwijk@ORA11G> insert into persons
2 ( id
3 , name
4 , bank_account_no
5 )
6 values
7 ( 1
8 , 'JOE'
9 , 123456789
10 )
11 /

1 rij is aangemaakt.

rwijk@ORA11G> insert into persons
2 ( id
3 , name
4 , bank_account_no
5 )
6 values
7 ( 2
8 , 'JANE'
9 , 183456789
10 )
11 /
insert into persons
*
FOUT in regel 1:
.ORA-02290: CHECK-beperking (RWIJK.PSN_ACCOUNT_ELEVEN_TEST_CK) is geschonden.


And now the rule is enforced declaratively. And it is faster than enforcing with database triggers. You can see a performance test that resembles this case at the end of this post.

So my conclusing is - until proven otherwise - that with Oracle11, all attribute rules can be enforced declaratively.


Tuple rules



The above shows two examples of tuple rules, but the one I've seen most often is the ubiquitous "startdate < enddate". Tuple rules are implemented in exactly the same way that other attribute rules are: mostly by check constraints. In real life I cannot recall ever having implemented a complex tuple rule for which a normal check constraint was not enough. But even if you have one, you can use the technique described above to implement it using a check constraint on a virtual column based on a deterministic function.

Next post: Implementing entity rules