Oracle 10gr2: prevent any dates that fall on a Sunday?

Question

Is it possible to use a CHECK constraint to prevent any date that falls on a Sunday? I don't want to use a trigger.

Answer 1

Not sure about the actual constraint, but you can use the function:

TO_CHAR(sysdate, 'D');

to get the day of the week as an integer, then do a small check on it

Answer 2

Yes, a check constraint can check that the day of the week is not Sunday. Here's an example:

create table date_test (entry_date date);

alter table date_test add constraint day_is_not_sunday
      check ( to_char(entry_date,'DAY','NLS_DATE_LANGUAGE = ENGLISH') not like 'SUNDAY%');

--There are blank spaces to the right of SUNDAY so like or rtrim is needed to match the string.

insert into date_test values(to_date('2008-10-12','YYYY-MM-DD')); --Sunday
insert into date_test values(to_date('2008-10-11','YYYY-MM-DD'));
insert into date_test values(to_date('2008-10-10','YYYY-MM-DD'));
insert into date_test values(to_date('2008-10-09','YYYY-MM-DD'));
insert into date_test values(to_date('2008-10-08','YYYY-MM-DD'));
insert into date_test values(to_date('2008-10-07','YYYY-MM-DD'));
insert into date_test values(to_date('2008-10-06','YYYY-MM-DD'));

When you try to insert a date that is on a Sunday, it will say:
ORA-02290: check constraint (SYS.DAY_IS_NOT_SUNDAY) violated