Skip to Main Content
  • Questions

  • Date constraint to validate all bookings are in the future

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Juliana.

Asked: March 20, 2019 - 4:10 pm UTC

Last updated: March 22, 2019 - 10:56 am UTC

Version: Oracle12

Viewed 1000+ times

You Asked

Hi,

I need to create a constraint which will not allow appointments to be booked in the past.
I wonder if someone could help me with this please.

Thank you.

Juliana

and Chris said...

Sadly you can't reference sysdate in a check constraint.

create table t (
  c1 date
);

alter table t 
  add constraint t_future
  check ( c1 > sysdate );

ORA-02436: date or system variable wrongly specified in CHECK constraint


So this is one case where a trigger can help you. Use this to raise an exception when someone tries to insert a date in the past:

create or replace trigger t_bi
before insert on t
for each row
begin
  if :new.c1 < sysdate then
    raise_application_error (
      -20001, 'No past bookings!'
    );
  end if;
end;
/

insert into t values ( sysdate );
insert into t values ( sysdate - 1 );

ORA-20001: No past bookings!

C1                     
20-MAR-2019 16:41:18


select * from t;

Rating

  (2 ratings)

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

Comments

Another Option?

Tubby, March 21, 2019 - 2:48 pm UTC

Perhaps changing your data model to store the Booking_Date as well as the Appointment_Date would be a good idea here. Then you could add a check constraint to validate that the appointment date is greater than the booking date, this approach also covers update scenarios if you need to. If you are using SYSDATE in a trigger the update scenarios and what is permissible (if anything) could get a bit muddied.

Cheers,
Chris Saxon
March 21, 2019 - 3:19 pm UTC

Great suggestion!

A reader, March 22, 2019 - 8:43 am UTC 


drop table tmp_test;

create table tmp_test (
  c1 date);

alter table tmp_test add 
  (b1 date invisible default sysdate, 
  constraint no_past_dates check (c1 >= b1)
  );

Chris Saxon
March 22, 2019 - 10:56 am UTC

I'd prefer to make this as a regular, visible column.
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

Design

New to good database design? Check out Chris Saxon's full fundamentals class.