And what about table constraints?

In a previous post we've introduced a classification scheme for constraints:

• attribute constraints
• tuple constraints
• table constraints
• database constraints
• dynamic constraints

And talked a bit about how we could implement the first two classes here. In today's post we will make a start talking about how we can implement table constraints using triggers. But before we do that we will offer some thoughts on how the ideal world with regards to this subject would look like.

Long ago, in a galaxy far away, an ANSI/ISO SQL standardization committee came up with the ground breaking concept of a SQL ASSERTION. SQL assertions would make our lives real easy when it comes down to implementing table constraints. The example constraint "we cannot have a manager without a clerk in the same department" could be implemented as:

create assertion managers_need_clerk as
check(not exists
        (select 'a department'
         from (select distinct deptno from emp) d
         where exists 
                 (select 'a manager in d'
                  from emp e
                  where e.deptno = d.deptno and e.job = 'MANAGER')
           and not exists
                 (select 'a clerk in d'
                  from emp e
                  where e.deptno = d.deptno and e.job = 'CLERK')
        )
     )
/

Presto. Done.
It would then be up to the DBMS to maintain this constraint. Of course we require the DBMS to do that in an efficient manner. This will all become clear during the course of the next couple of posts.

By the way you may think, why not just do this with a CHECK constraint?

alter table emp add constraint managers_need_clerk as
check(not exists
        (select 'a department'
         from (select distinct deptno from emp) d
         where exists 
                 (select 'a manager in d'
                  from emp e
                  where e.deptno = d.deptno and e.job = 'MANAGER')
           and not exists
                 (select 'a clerk in d'
                  from emp e
                  where e.deptno = d.deptno and e.job = 'CLERK')
        )
     )
/

Well that's because CHECK constraints do not allow sub-queries. They don't allow that for the same reason as why we still do not have support for assertions. Both require the DBMS vendor to produce some seriously complex piece of software that can accept an arbitrary complex boolean SQL-expression and compute from that the most efficient way to maintain that boolean expression inside all concurrently running transactions. The research and development effort for this still needs to be done.


So, we have to resort to some other means when it comes to implementing table constraints. Here's a list of possible implementation strategies.

So we've discussed the first one already: it's highly preferred, but unfortunately only a very partial solution. The only table constraints that we can deal with declaratively are:

• Keys (be them primary or unique), and
• Foreign keys, in case the FK refers back to another column in the same table (in which case the foreign key is a table constraint, and not a database constraint).

The trigger approach is what the rest of this blog will be all about. Every table constraint can be implemented using triggers. Contrary to popular belief this is doable. But it *is* rather complex. The fact that this is a full solution for the table constraint class, is a big pro though.

Then there is the API-approach. This is the approach where you encapsulate all DML statements inside stored procedures, and disallow any direct DML access to your tables. The only way to modify your tables is through the stored procedure API-layer. And inside this API-layer, you deal with constraint validation, just before or right after you issue the DML statements. To me this is a fundamentally flawed solution, since it will always lead to constraint enforcing code duplication. And since it is not a "once and for all" solution. Every time you maintain your application and need to introduce new transactions, you'll have to take care of constraint enforcement again. Many people also tend to completely disregard the complexities involved. To a certain extent, they are the exact same complexities as are involved in the trigger approach: you'll have to take care of serialization and efficiency (all explained in future posts) in this approach too. And finally, in practice it is very difficult to maintain the enforcement of only allowing access to you tables via the API-layer. At some point people will find ways to go around the API-layer, and thereby be able to introduce corrupt data.

And there is the function based index trick. This is where we can use unique, function-based, indexes to implement keys across a subset of rows in a table. This too is a very partial solution for implementing table constraints. I'm assuming, since you read this blog, you are familiar with this trick, otherwise let me know in a comment and I'll provide you with an example.

Another approach is to employ materialized views in an ingenious manner. Like triggers, this is in theory a full solution, but in practice only a very partial one. It all boils down to the same research and development effort mentioned above when we discussed assertions, not having been done yet. We will discuss the materialized view approach in our next post.

Stay tuned.

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.