|
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.
|
