Implementing a check constraint with sysdate in it, is not possible. Not until 11g that is, where you can use a trick to do this using virtual columns. Here is a small demo:
First, let's create a table and try to place a check constraint to check that a date should be a date in the past:
rwijk@ORA11G> create table mytable
2 ( id number(4)
3 , mydate date
4 )
5 /
Tabel is aangemaakt.
rwijk@ORA11G> alter table mytable add constraint myck1 check (mydate < sysdate)
2 /
alter table mytable add constraint myck1 check (mydate < sysdate)
*
FOUT in regel 1:
.ORA-02436: Datum- of systeemvariabele is onjuist opgegeven in CHECK-beperking.
As expected, ORA-02436 (date or system variable wrongly specified in CHECK constraint) is returned.
Wrapping the check inside a function does not help:
rwijk@ORA11G> create function mydate_in_past
2 ( p_mydate in mytable.mydate%type
3 ) return varchar2
4 is
5 begin
6 return case when p_mydate >= sysdate then 'N' else 'Y' end;
7 end mydate_in_past;
8 /
Functie is aangemaakt.
rwijk@ORA11G> alter table mytable
2 add constraint myck1 check (mydate_in_past(mydate) = 'Y')
3 /
add constraint myck1 check (mydate_in_past(mydate) = 'Y')
*
FOUT in regel 2:
.ORA-00904: "MYDATE_IN_PAST": ongeldige ID
Since a function is not allowed inside a check constraint. So let's add the function as a virtual column to the table:
rwijk@ORA11G> alter table mytable
2 add (mydate_in_past_ind as (mydate_in_past(mydate)))
3 /
add (mydate_in_past_ind as (mydate_in_past(mydate)))
*
FOUT in regel 2:
.ORA-30553: De functie is niet deterministisch.
Oracle is clever enough to know that you can only use deterministic functions for virtual columns. And a function using sysdate is never deterministic of course. Validating input of 25-12-2007 will give FALSE as a result, but tomorrow it will be TRUE. But let's try to fool Oracle by just saying the function is deterministic, where it is not:
rwijk@ORA11G> create or replace function mydate_in_past
2 ( p_mydate in mytable.mydate%type
3 ) return varchar2 deterministic
4 is
5 begin
6 return case when p_mydate >= sysdate then 'N' else 'Y' end;
7 end mydate_in_past;
8 /
Functie is aangemaakt.
rwijk@ORA11G> alter table mytable
2 add (mydate_in_past_ind as (cast(mydate_in_past(mydate) as varchar2(1))))
3 /
Tabel is gewijzigd.
rwijk@ORA11G> desc mytable
Naam Null? Type
-------------------------------------------------------------------------- -------- --------------------
ID NUMBER(4)
MYDATE DATE
MYDATE_IN_PAST_IND VARCHAR2(1)
It works! And now you can check whether mydate is a date in the past:
rwijk@ORA11G> alter table mytable
2 add constraint myck1 check (mydate_in_past_ind = 'Y')
3 /
Tabel is gewijzigd.
So we have fooled Oracle by saying the function is deterministic and we can reason that in this case it is not bad, because when a row satisfies the constraint, it will keep satisfying the constraint as time passes.
Let's check if the constraint works:
rwijk@ORA11G> insert into mytable (id,mydate) values (1,date '2007-01-01')
2 /
1 rij is aangemaakt.
rwijk@ORA11G> insert into mytable (id,mydate) values (2,date '2020-01-01')
2 /
insert into mytable (id,mydate) values (2,date '2020-01-01')
*
FOUT in regel 1:
.ORA-02290: CHECK-beperking (RWIJK.MYCK1) is geschonden.
Great, but you have to be careful.
Watch what happens when checking whether a date is in the future. This cannot ever be a real constraint, as there will always be a time that a date will not be in the future anymore. But using the same technique as above it is possible:
rwijk@ORA11G> rollback
2 /
Rollback is voltooid.
rwijk@ORA11G> alter table mytable add (mydate2 date)
2 /
Tabel is gewijzigd.
rwijk@ORA11G> create function mydate_in_future
2 ( p_mydate in mytable.mydate%type
3 ) return varchar2 deterministic
4 is
5 begin
6 return case when p_mydate > sysdate then 'Y' else 'N' end;
7 end mydate_in_future;
8 /
Functie is aangemaakt.
rwijk@ORA11G> alter table mytable
2 add (mydate2_in_future_ind as (cast(mydate_in_future(mydate2) as varchar2(1))))
3 /
Tabel is gewijzigd.
rwijk@ORA11G> alter table mytable
2 add constraint myck2 check (mydate2_in_future_ind = 'Y')
3 /
Tabel is gewijzigd.
rwijk@ORA11G> desc mytable
Naam Null? Type
-------------------------------------------------------------------------- -------- -----------------
ID NUMBER(4)
MYDATE DATE
MYDATE_IN_PAST_IND VARCHAR2(1)
MYDATE2 DATE
MYDATE2_IN_FUTURE_IND VARCHAR2(1)
rwijk@ORA11G> insert into mytable (id,mydate2) values (1,date '2007-01-01')
2 /
insert into mytable (id,mydate2) values (1,date '2007-01-01')
*
FOUT in regel 1:
.ORA-02290: CHECK-beperking (RWIJK.MYCK2) is geschonden.
rwijk@ORA11G> insert into mytable (id,mydate2) values (2,date '2020-01-01')
2 /
1 rij is aangemaakt.
So it is quite easy to misuse this feature. But when used carefully it is useful for implementing more business rule declaratively.
Previously, this type of constraint would be implemented using a database trigger. But we all know by now :-) that database triggers are evil. For those of us that don't know this yet, let's do a small performance test, to prove this once again, after dropping all previous objects:
rwijk@ORA11G> create function mydate_in_past
2 ( p_mydate in date
3 ) return varchar2 deterministic
4 is
5 begin
6 return case when p_mydate >= sysdate then 'N' else 'Y' end;
7 end mydate_in_past;
8 /
Functie is aangemaakt.
rwijk@ORA11G> create table mytable_virtual_column
2 ( id number(6)
3 , mydate date
4 , mydate_in_past_ind as (cast(mydate_in_past(mydate) as varchar2(1)))
5 , constraint myck1 check (mydate_in_past_ind = 'Y')
6 )
7 /
Tabel is aangemaakt.
rwijk@ORA11G> create table mytable_database_trigger
2 ( id number(6)
3 , mydate date
4 )
5 /
Tabel is aangemaakt.
rwijk@ORA11G> create trigger mytrigger
2 before insert or update on mytable_database_trigger
3 for each row
4 begin
5 if inserting
6 or updating('mydate')
7 then
8 if :new.mydate >= sysdate
9 then
10 raise_application_error(-20000,'mydate must be a date in the past.');
11 end if;
12 end if;
13 end;
14 /
Trigger is aangemaakt.
rwijk@ORA11G> exec runstats_pkg.rs_start
PL/SQL-procedure is geslaagd.
rwijk@ORA11G> insert into mytable_virtual_column (id,mydate)
2 select level
3 , date '2007-01-01'
4 from dual
5 connect by level <= 100000
6 /
100000 rijen zijn aangemaakt.
rwijk@ORA11G> exec runstats_pkg.rs_middle
PL/SQL-procedure is geslaagd.
rwijk@ORA11G> insert into mytable_database_trigger (id,mydate)
2 select level
3 , date '2007-01-01'
4 from dual
5 connect by level <= 100000
6 /
100000 rijen zijn aangemaakt.
rwijk@ORA11G> exec runstats_pkg.rs_stop(100)
Run1 draaide in 42 hsecs
Run2 draaide in 172 hsecs
Run1 draaide in 24,42% van de tijd
Naam Run1 Run2 Verschil
STAT.DB time 43 168 125
STAT.CPU used when call started 42 169 127
STAT.CPU used by this session 39 169 130
STAT.redo size 2,458,396 2,458,560 164
LATCH.object queue header operation 773 994 221
STAT.free buffer inspected 0 250 250
Run1 latches totaal versus run2 -- verschil en percentage
Run1 Run2 Verschil Pct
15,027 15,528 501 96.77%
PL/SQL-procedure is geslaagd.
I repeated the test several times, and got consistent results: using declarative constraints with virtual columns is approximately four times faster than using those dreaded database triggers.
great solution!
ReplyDeleteWhat happens when someone's insert fails because the date is in the future (let's say by one minute), and then two minutes later they try again when the date is no longer in the future. Couldn't the previous DETERMINISTIC call prevent the now-valid (no longer in future) date from being allowed since Oracle could remember the value from the prior execution?
ReplyDeleteIf you are in the same session, then yes, the deterministic clause will cache the result the first time and produce the same result the second time.
ReplyDeleteWhat happens when someone tries to create an index on the virtual column?
ReplyDelete