Skip to Main Content
  • Questions

  • Prevent future dates in for inspection_date column in table

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Michael.

Asked: February 02, 2016 - 3:05 pm UTC

Last updated: September 04, 2024 - 2:20 am UTC

Version: 11.2

Viewed 1000+ times

You Asked

I have a table called DAY_INSP and a column INS_DATE which allows dates in the future. I want to prevent that by only allowing today's date or older. Do I need to add a trigger or constraint to do this? Any help would be appreciated

and Chris said...

You can't use sysdate in a check constraint. You can do this with triggers:

SQL> create table t (
  2    dt date
  3  );
SQL>
SQL> alter table t add constraint ck check (dt < sysdate);
alter table t add constraint ck check (dt < sysdate)
                                            *
ERROR at line 1:
ORA-02436: date or system variable wrongly specified in CHECK constraint


SQL>
SQL> create or replace trigger tt
  2  before insert or update on t
  3  for each row
  4  begin
  5    if :new.dt > sysdate then
  6      raise_application_error(-20001, 'Date in the future!');
  7    end if;
  8  end;
  9  /
SQL> show err
No errors.
SQL>
SQL> insert into t values (sysdate+1);
insert into t values (sysdate+1)
            *
ERROR at line 1:
ORA-20001: Date in the future!
ORA-06512: at "CHRIS.TT", line 3
ORA-04088: error during execution of trigger 'CHRIS.TT'

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

madhava, August 28, 2024 - 9:18 am UTC

Hi, Can you please explain why we can not use sysdate in check constraint. Can you please explain the full details of this ?

Basically in our case we are in the decom path of our application/db so we dont want to invest much into this solution so that of going with easy approach of using check constraints date check option. we see there isn't much risk of this?
Connor McDonald
September 01, 2024 - 9:41 am UTC

Consider the following

create table t ( y int, x date check x < sysdate +1 );

I insert a row today... its valid.
Tomorrow I update Y to some value. X is no longer valid. I assume that is not the behaviour you would want but its what a constraint is designed to do

Workaround ?

Jim Dickson, September 03, 2024 - 2:24 am UTC

Just thinking aloud- this might work

Add new column e.g. row_inserted, perhaps invisible, default value = sysdate
- note this is now a static value
Add constraint on INS_DATE < row_inserted

Chris Saxon
September 03, 2024 - 12:40 pm UTC

It could work - though you have to be certain no one can set the value for row_inserted!

Workaround 23ai ?

Jim Dickson, September 03, 2024 - 3:05 am UTC

My original idea (might) work only for inserts.
In 23ai, this might be suitable to cover updates too
https://oracle-base.com/articles/23/default-on-null-for-insert-and-update-23

Chris Saxon
September 03, 2024 - 12:42 pm UTC

You have to explicitly set the column to null in an update for this to work; just omitting it from the list of columns you set has no effect.

So in theory this could help. In practice it probably doesn't

Did I miss understand something?

Greg, September 03, 2024 - 5:16 pm UTC

You responded:

>> Consider the following
>>
>> create table t ( y int, x date check x < sysdate +1 );
>>
>> I insert a row today... its valid.
>> Tomorrow I update Y to some value. X is no longer valid. I assume that is not the behaviour you would want but its what a constraint is designed to do

however, I'm not following your logic here.

if the check constraint was set to "x < sysdate +1" I don't see why that would not still be valid ?

If the insert happened on Sep 2, then x = sep 2. Check constraint compares x (sep 2) < sysdate + 1 (sep 3) which is true. all good.

Then the update on Sep 3 ... Check constraint still sees x as (sep 2), and compares thatn to sysdate + 1 (now Sep 4) . which is still true ??

Even if the update updates the dt field ... to sep 3, it's comparing sep 3 < sep 4 .. which is still valid?


Connor McDonald
September 04, 2024 - 2:20 am UTC

My point is - its not deterministic. At any given point in time, the data could be instantaneously valid or instantaneously invalid.


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library