oracle sql check constraint condition met

Question

Beginner question: Is there a way to write a check constraint that restricts data where a condition is met. For instance, if I want to not allow appointment times between 1100 and 1300 on a specific day.

ALTER TABLE appointment_info
 ADD CONSTRAINT ap_time_ck CHECK (ap_time NOT BETWEEN 11 and 13);

I tried

ALTER TABLE appointment_info
 ADD CONSTRAINT ap_time_ck CHECK (ap_time NOT BETWEEN 11 and 13 AND ap_date != '02-APR-2014');

But obviously that will restrict all appointment times between 1100 and 1300 and any appointments on April 2nd.

thats what i thought but my check constraint is violated because i have an appointment set for april 2nd at 1400. — Geoff Lentsch
– Geoff Lentsch, Commented Mar 10, 2014 at 14:59

smnbbrv · Accepted Answer · 2014-03-10 14:59:17Z

1

That's possible solution:

ALTER TABLE appointment_info
ADD CONSTRAINT ap_time_ck CHECK (not (ap_time BETWEEN 11 and 13 and ap_date = date '2014-04-02'));

I am against using this: '02-APR-2014' because it can depend on NLS settings.

Or just change and condition to or in your attempt. It would also work.

ALTER TABLE appointment_info
ADD CONSTRAINT ap_time_ck CHECK (ap_time NOT BETWEEN 11 and 13 OR ap_date != '02-APR-2014');

answered Mar 10, 2014 at 14:59

smnbbrv

24.7k9 gold badges85 silver badges115 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Geoff Lentsch Over a year ago

Thank you so much. I used the first solution you gave.

Collectives™ on Stack Overflow

oracle sql check constraint condition met

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related