A little more on check constraints (why don’t I know this stuff already)

In the same meeting I spoke of yesterday, the developer whose code we were reviewing had a check constraint on a year_of_birth column defined as ‘year_of_birth between 1900 and 9999′.  I quickly filed it under <keep it professional Chris!> and suggested that the constraint offered no benefits.  I confidently suggested that what he really wanted was ‘year_of_birth <= extract(year from sysdate)’.  Maybe he would want to make sure the person didn’t claim to be older than 115 years or something but he certainly didn’t want to allow years from the future.

Later that afternoon I get an email stating that my suggestion resulted in an ORA-02436.  Oops.  It turns out that check constraints cannot contain calls to functions that are not deterministic.  Ugh.  I should have known that. So much for this blog advertising what a knowledgeable Oracle guy I am!

Anyways, I responded with a list of three options.

1. This was a very modestly sized APEX app.  Validating the data through APEX validations and calling it a day seemed perfectly reasonable.  The other two options provide no additional information to the optimizer that check constraints potentially do.  Sure you want data rules as close to the data as possible but this is so trivial that I don’t see it mattering much.
2. Define a trigger that raises an exception if the :new value violates the rule.  I generally despise triggers.  I’ve wasted far too much of my life trying to track down the source of some unexplained event only to eventually discover it has something to do with triggers.  They complicate things far more often than they simplify them.  However, I think it is commonly acknowledged that a valid use for triggers is data validation that cannot be done declaratively.  In fact I think I’ll be hearing a presentation given by Toons Koppelaars making that very same claim at Hotsos this year.
3. We just upgraded to 11G so you can play games with virtual columns but its really a kludge and I could imagine a day when the database kernel wises up to the trick in order to prevent the problems the restriction is meant to address.  I discouraged this.

In the end, I think the developer is going to go with both 1 and 2.  That way you prevent unecessary submits back to the database and protect yourself against bad data getting in through some route other than the application.

In case anyone wants to implement an non-deterministic function into a constraint here’s an example that you can tailor to your needs (I lifted this idea from here :

create or replace function year_in_past( in_year in table.year_of_birth%type) return varchar2 deterministic is
begin
return case when in_year >= extract(year from sysdate) then ‘N’ else ‘Y’ end;
end year_in_past;
/
alter table table1   add (year_in_past_ind as (cast(year_in_past(year_in_past) as varchar2(1))));

alter table table1 add constraint yip_ck1 check (year_in_past_ind = ‘Y’);

This wouldn’t work if you wanted to ensure a year was set to some future value since, as we all know, time marches on and what once was the future always becomes the past.

Ok, I will endeavor to post something a little farther from DBA 101 next time.

Chris

Advertisement

---