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

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

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