Oracle CHECK constraint for checking values ​​between columns

Question

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

I want to add restrictions for type and price as:

• if one (s), then the price should not be more than 50,
• if double (d), then the price should not exceed 100, and
• if the family (f), then the price should not exceed 150

I tried to add it like this, but it gave me an error. Not sure how to 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))); 

Received error:

 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 looks like you need to OR three conditions together, rather than AND them together. It is impossible for any string to satisfy all three criteria: type cannot have the values ​​S, D and F. at the same time. Probably you 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 need to use "or" instead of "and" in the three validation conditions.

And the error message (ORA-02293: cannot verify) reports that your table has several rows of data that violate the integrity constraint. In addition, you can specify whether existing data in the table should be constrained or not with the VALIDATE or NOVALIDATE parameter. if you do not want to check these existing data rows in the room, you can specify this restriction using NOVALIDATE, and VALIDATE by 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 ); 

Answer 3

You receive an ORA-02293 error because you already have data in your table that does not satisfy your newly created validation constraint.

I think the important part here is to understand that you do not have one business rule to check here, but three. And it would be very convenient for the user who checks the restriction to know EXACTLY what is wrong with the line that he inserts. Therefore, why not go for these restrictions:

 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 

Yours faithfully,
Rob

PS: More information on this topic can be found in this blog post box .