Assertions

I have two tables, one called nurses and the other called pharmacist. Both tables record their license number. I need to write an assertion that ensures that all licenses of nurses and pharmacists have been renewed in the last two years. Thank you for your help.

Congratulations, you've stumped me. Actually, this happens quite frequently, but usually because a question is submitted to Ask the Expert with insufficient information, or phrased ambiguously.

In this case, however, there's no ambiguity. The problem is quite clear. I just don't know the answer.

An assertion is a statement in SQL that ensures a certain condition will always exist in the database. Assertions are like column and table constraints, except that they are specified separately from table definitions. An example of a column constraint is NOT NULL, and an example of a table constraint is a compound foreign key, which, because it's compound, cannot be declared with column constraints.

An example of an assertion is:

create assertion recent_licenses
 check (
   ( select count(*) 
       from nurses
      where license_renewal_date 
          < '2002-01-01' ) = 0
       )

Now here's the problem. How could we specify an assertion that refers to "the last two years"? Certainly, we could try using the CURRENT_DATE keyword, as well as INTERVAL arithmetic to establish a date two years in the past. We might even be successful in declaring the assertion. (Not all database systems support assertions.)

However, assertions are checked only when UPDATE or INSERT actions are performed against the table. So we can postulate a situation where the assertion has been defined, all the nurses are licensed within the last two years, and then no changes are made against the table for three years. The assertion would be violated!

Besides, you may have difficulty composing the assertion in a manner that will even be accepted by your database system. You will likely save yourself a lot of heartache and effort if you simply set up a periodic scheduled task or cron job to run a query and report on any licenses that have expired.