CHECK constraint in oracle for value check between the columns

Question

I have a table called Room and it has columns (ID, type, price...etc)

I want to add constraints for both type and price like:

• if single (s), then price should not be greater than 50,
• if double (d), then price should not be greater than 100, and
• if family (f), then price should not be greater than 150

I tried to add it like this but it's giving me an error. Not sure how should I write this:

ALTER TABLE ROOM 
ADD (CONSTRAINT CHK_PRICE CHECK (
(TYPE='S' AND PRICE <= 50) AND 
(TYPE='D' AND PRICE <=100) AND 
(TYPE='F' AND PRICE <= 150)));

The error received is:

SQL Error: ORA-02293: cannot validate (xxxx.CHK_PRICE) - check
constraint violated
02293. 00000 - "cannot validate (%s.%s) - check constraint violated"
*Cause:    an alter table operation tried to validate a check constraint to
           populated table that had nocomplying values.
*Action:   Obvious

Solution 1

It sounds like you need to OR together the three conditions, not AND them together. It is impossible for any row to satisfy all three criteria-- type cannot simultaneously have a value of S, D, and F. You probably want

ALTER TABLE ROOM 
  ADD (CONSTRAINT CHK_PRICE CHECK (
        (TYPE='S' AND PRICE <=  50) OR -- <-- OR, not AND
        (TYPE='D' AND PRICE <= 100) OR -- <-- OR, not AND
        (TYPE='F' AND PRICE <= 150)));

Solution 2

Well, you do need to use "or" instead of "and" in your three check conditions.

And error message (ORA-02293: cannot validate) informs that there're some data rows in your table room violating your integrity constraint. Additionally, you can specify whether existing data in the table must conform to be constraint or not with the option VALIDATE or NOVALIDATE. if you do not want do validate these existing data rows in room, you can specify this constraint with NOVALIDATE, and VALIDATE is default.

    ALTER TABLE ROOM    
    ADD (CONSTRAINT CHK_PRICE CHECK 
    (
    (TYPE='S' AND PRICE <=  50) OR -- <-- OR, not AND
    (TYPE='D' AND PRICE <= 100) OR -- <-- OR, not AND
    (TYPE='F' AND PRICE <= 150)
    )
    NOVALIDATE   -- VALIDATE is default
    );

Solution 3

You get the error message ORA-02293 because you already have data in your table that does not satisfy your newly created check constraint.

I think an important part here, is to realize that you don't have one business rule to validate here, but three. And it would be most convenient for the user who validates the constraint, to know EXACTLY what is wrong with the row he's inserting. So that's why I'd go for these constraints:

SQL> create table room (id,type,price)
  2  as
  3  select 1, 'S', 50 from dual union all
  4  select 2, 'D', 80 from dual union all
  5  select 3, 'F', 110 from dual
  6  /

Table created.

SQL> alter table room add constraint single_room_below_50 check (type != 'S' or price <= 50)
  2  /

Table altered.

SQL> alter table room add constraint double_room_below_100 check (type != 'D' or price <= 100)
  2  /

Table altered.

SQL> alter table room add constraint family_room_below_150 check (type != 'F' or price <= 150)
  2  /

Table altered.

SQL> insert into room values (4, 'S', 60)
  2  /
insert into room values (4, 'S', 60)
*
ERROR at line 1:
ORA-02290: check constraint (RWIJK.SINGLE_ROOM_BELOW_50) violated

Regards,
Rob.

PS: More background on this subject can be found in this blogpost of mine.

Comments

[Brian Camire]

@user1672735 might also want/need to add conditions (either in this check constraint or in separate non-null constraints) to handle or prevent cases where TYPE and/or PRICE are null. For example, the modified constraint as posted will, on its own, not prevent rows from being inserted if either TYPE or PRICE is null, which may not be what is intended.