Implementing attribute and tuple constraints

In our previous post we have introduced a classification scheme for data integrity constraints. In todays post we will present thoughts & guidelines around how to implement the first two classes:  attribute and tuple constraints.

The examples given in the previous post were:

Attribute constraints

• Salary must be a positive integer between 750 and 14000.
• Job must be one of the following list: MANAGER, CLERK, SALESMAN, etc.

Tuple constraints

• A manager cannot have a salary less than 5000.
• An employee working in department 10, cannot be a salesman.

We could implement these constraints using a trigger approach. Let's go down that road first since this blog is about triggers. We will have to figure out then which trigger types (we have 12 of them, remember) are best fit to implement these attribute and tuple level constraints?

• Row triggers or statement triggers?
• Firing before or after the DML statement?
• Firing when? On insert, update or delete?

A bit of thought quickly leads to:

• Row triggers, since they have the :old and :new variables that enable us to easily inspect the column value(s) involved in the constraint;
• Before triggers seem obvious since we can prevent the rows actually being processed when they are in violation with one of these constraints.
• And finally, the delete event is of no concern. We only require validation during insert and update. And even then *only* when columns involved in any of the constraints are affected.

Here is the trigger code that could implement the first attribute constraint example mentioned above. Not too difficult.

create or replace trigger emp_aiur1
before insert or update on EMP
for each row
begin
  --
  if INSERTING or UPDATING('SALARY')
  then
     --
     if :new.SALARY < 750 or :new.SALARY > 14000
     then
       --
       raise_application_error(-20000,'Salary must be between 750 and 14000.');
       --
     end if;
     --
  end if;
end;
/

Note that this isn't actually an example of our fourth use-case: that use-case required triggers to execute queries. But as it so happens to be, using a trigger for implementing attribute (or tuple as we'll see hereafter) constraints doesn't require us to code any queries.

Similarly we can code a trigger to implement a tuple constraint. Here's the trigger for the first tuple constraint example given above:

create or replace trigger emp_aiur2
before insert or update on EMP
for each row
begin
  --
  if INSERTING or UPDATING('JOB') or UPDATING('SALARY')
  then
     --
     if :new.JOB = 'MANAGER' and :new.SALARY < 5000
     then
       --
       raise_application_error(-20000,'A manager cannot earn less than 5000.');
       --
     end if;
     --
  end if;
end;
/

Note the numbering that was introduced in the trigger names above. Going down the road of using triggers to implement attribute and tuple constraints, also requires us to think about this. Do we combine all code for these constraints into one "after insert or update for each row" trigger per table? Seems plausible: for one we then control the order in which the validations occur. Yes, I'm aware of the FOLLOWS construct, we could use that also and keep the separate triggers. But it doesn't make the solution more elegant nor better maintainable, does it?

Instead of the procedural approach above, a much, much better approach for implementing attribute and tuple constraints is of course to use the declarative CHECK syntax. Here's the equivalent for above two triggers:

alter table emp add constraint EMP_SAL1
check(SALARY between 750 and 14000)
/

alter table emp add constraint EMP_JOB_SAL1
check(JOB != 'MANAGER' or SALARY >=5000)
/

The second CHECK clause might be difficult for you at first sight. That depends whether you know the important rewrite rule that we have in formal logic: how to rewrite an implication (IF...THEN...) into a disjunction (...OR...). Whenever a predicate A implies a predicate B, written as 'A implies B', then we can rewrite that as 'not A or B'. And the tuple constraint at hand is indeed an implication (as so many tuple rules are btw): if job is MANAGER then salary must be greater than or equal to 5000.

The same question also pops up when using CHECK constraints: do we combine them all into one CHECK constraint (by logically AND-ing them all), or have separate CHECK constraints? My strong preference is to implement them as separate CHECK constraints. Granted you do not control the order in which they 'fire' in this case, but you do get better (ie. more detailed) errors.

In our next post we will proceed to guidelines/thoughts around implementing multi-row (table and database) constraints.

Thanks for tuning in.