I couldn't find very many examples of writing CHECK constraints (table, not domain) in
Interbase/Firebird, but did come across enough Oracle examples to give me an idea of what I was
shooting for.

Mentioned with the Oracle examples was the inability to do sub-select queries in the CHECK
constraint, and that some variables like the current date would be unavailable. So I tested this
out with Firebird. We're adding something like the following to the database. (Test only, don't
worry, we haven't done it to our production database, and probably won't. I was exploring.)

set term !!;

create procedure is_enrolled (p_affiliation_id integer, p_current_date date)
returns (result integer)
as
declare variable join_date date;
declare variable relinquish_date date;
declare variable cdib varchar(250);
declare variable death_date date;
begin
select A.join_date, A.relinquish_date, A.cdib, P.death_date
from affiliations A inner join people P on A.person_id = P.id
where A.id = :p_affiliation_id
into :join_date, :relinquish_date, :cdib, :death_date;
if (:cdib is not null and death_date is null and :join_date < :p_current_date and
(:relinquish_date is null or :relinquish_date > :p_current_date)) then result = 1;
else result = 0;
suspend;
end
!!
set term ; !!

/* id_domain is integer not null with default 0 */
alter table affiliations add dummy_field id_domain;

alter table affiliations add constraint not_stupid check (not exists(select result from is_enrolled
(affiliations.id, 'today') where affiliations.dummy_field is not null));

We added the SP, and it made sense for what we were doing (making it easy in listviews to tell if a
person is currently enrolled, or on some screens, see if they were enrolled as of a given date,
like the date on which they went to the hospital, and still had benefits from us.) The other is a
constraint we were thinking of adding to make sure you could never give a reason why someone wasn't
enrolled, if they were in fact enrolled. We don't often use database constraints (naughtily doing
it mostly client-side), and probably won't in this case (favoring update triggers instead to make
the "reason" (here as "dummy_field") go away.)

Main question:

In firebird, what's the effect of a constraint failing at a later point in time because of the use
of a variable like 'today'? Will it fail the next time someone does anything to the table, and some
row (not necessarily affected by the update/insert/delete) no longer matches the constraint? (I
wouldn't think Firebird would constaintly check constraints at random, without any updates
happening.) And once that happens, what are you expected to do about it, other than drop the
constraint?

-Philip