Enforcing complex constraints in Oracle

Oracle supports various kinds of declarative integrity constraints:

• Primary Key: Uniquely identifies a row in the table
• Unique: Other columns that must be unique
• Foreign Key: Column value must match value in another table
• Check: Simple single-table, single-row data rules.

Examples of possible check constraints are:
“start_date <= end_date”
“check_ind in (‘Y’,’N’)”
“amount between 0 and 99999.99”

However, many more complex business rules cannot be implemented via the above constraints. For example:

• Employee Project Assignment start date and end date must fall between Project start date and end date
• An employee may not have 2 Employee Project Assignments that overlap date-wise
• A Project cannot have more than 10 Employees Assignments
• An Employee cannot book time to a Task on a Project to which he is not assigned
• The manager of a Department must belong to the Department.

These are usually enforced (if at all) procedurally, by one of the following methods:

• Code in the application screens
• Code in stored procedures (APIs) called from the application screens
• Database triggers

These all have disadvantages compared to the declarative approach of constraints:

• Code in application screens can be bypassed, and also the same rule must often be implemented in many places (e.g. in both the Project screen and the Assignment screen).
• Code in stored procedures must also often be implemented in many places (e.g. in both the Project package and the Assignment package). Also, to prevent bypassing of the rules, all updates must be done via the package, which limits the functionality available to the user (cannot write ad hoc updates)
• Code in triggers must also often be implemented in many places (e.g. in both the Project triggers and the Assignment triggers). Also, triggers can often become complex due to work-arounds to avoid mutating tables issues etc.
• Any procedural solution must explicitly lock records to prevent corruption in a multi-user environment – e.g. if a user is amending an Employee Assignment, then no other user may be allowed to amend Employee Assignments for the same employee, or to amend the Project dates.

This paper shows how such complex constraints can be implemented declaratively, such that the rules are defined once and then applied to all updates from whatever source.

This began with an on-line discussion I had with Tom Kyte about enforcing complex constraints here: http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:21389386132607

Objective

Ideally, given a complex rule such as one of the examples above, we would like to be able to create a complex check constraint (or “assertion” in ANSI SQL terms) such as:

CONSTRAINT c ON TABLE project p
CHECK (NOT EXISTS (SELECT null FROM emp_proj_assign ep
WHERE ep.projno = p.projno
AND (ep.start_date <> p.end_date)));

Such a constraint (if it could be implemented) would raise an error in any of the following situations:

• User attempts to insert an emp_proj_assign record with dates outside the Project dates
• User attempts to update an emp_proj_assign record with dates outside the Project dates
• User attempts to update a Project record with dates that do not encompass all the associated emp_proj_assign dates

However, we can’t achieve that via constraints alone.

Solution: use Materialized Views

Materialized Views (a.k.a. Snapshots) are actually tables that are maintained by Oracle, whose contents correspond to the result of a query – i.e. like a view, but “materialized” because the result is actually stored in a table.

These provide the mechanism we need to implement the complex constraint as follows:

• Create a materialized view to select data that violates the desired constraint (e.g. assignments where the dates are outside the associated project dates). The MV must be defined with REFRESH COMPLETE ON COMMIT so that it is updated before the end of the transaction.
• Create a check constraint on the materialized view that always evaluates to FALSE – e.g. CHECK (1=0)

That’s it. Whenever the underlying tables are updated, the materialized view is refreshed. If the update violates the rule, then a row will be inserted into the materialized view; but the check constraint on the MV disallows any inserts into it, and so the transaction fails.

Issues

• Oracle 8i cannot support REFRESH ON COMMIT on materialized views of the complexity required for some rules. 9i can handle some but not all (does not allow self-joins in MVs).. 10G can handle self-joins, but does not seem to allow subqueries. So this approach cannot be used for all rules.
• Efficiency: needs to be benchmarked. Is a FAST refresh preferable to a COMPLETE refresh? With COMPLETE we have a query that looks at all rows of the MV’s base tables at the end of every transaction that affects those tables.
• Cannot create a REFRESH ON COMMIT materialized view with a HAVING clause. For such cases (see example 3 below), the materialized view cannot include the constraint violation (e.g. HAVING COUNT(*) > 10), and so the check constraint must do it (e.g. CHECK (cnt <= 10)). Note that in this case the materialized view will consume space in the database.

Worked Examples

Based on the following tables:

create table project
( projno int primary key
, start_date date not null
, end_date date not null
);
create table emp_proj_assign
( empno int not null
, projno int not null
, start_date date not null
, end_date date not null
, primary key (empno, start_date)
);

1) Rule: An employee cannot have overlapping project assignments.

This is implemented as follows:

create materialized view emp_proj_mv1
refresh complete on commit as
select 1 dummy
from emp_proj_assign ep1, emp_proj_assign ep2
where ep1.empno = ep2.empno
and ep1.start_date <= ep2.end_date
and ep1.end_date >= ep2.start_date;


alter table emp_proj_mv1
add constraint emp_proj_mv1_chk
check (1=0) deferrable;

2) An employee's project assignment dates must fall between the project start
and end dates

create materialized view emp_proj_mv2
refresh complete on commit as
select 1 dummy
from emp_proj_assign ep, project p
where ep.projno = p.projno
and (ep.start_date <> p.end_date);
alter table emp_proj_mv2
add constraint emp_proj_mv2_chk
check (1=0) deferrable;

3) A project may not have more than 10 assignments (in total, ever!):

create materialized view emp_proj_mv3
build immediate
refresh complete on commit as
select projno, count(*) cnt
from emp_proj_assign
group by projno;
alter table emp_proj_mv3
add constraint emp_proj_mv3_chk
check (cnt <= 10)
deferrable;

4) A project cannot have more than 10 employees assigned at the same time.

(I have not yet worked this one out!)