CODEWITHSUNDEEP
sqloracle-database
Geoff Lentsch
Geoff Lentsch

Reputation: 1144

oracle sql check constraint condition met

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.

Upvotes: 1

Views: 125

Answers (1)

smnbbrv
smnbbrv

Reputation: 24581

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');

Upvotes: 1

Related Questions