预言机中的 CHECK 约束，用于列之间的值检查

Question

我有一个名为Room的表，它有列（ID，类型，价格...等）

我想为type和price添加约束，例如：

• 如果是单价，则价格不应大于50，
• 如果双倍 （d），则价格不应大于 100，并且
• 如果家庭（F），则价格不应大于150

我

试图像这样添加它，但它给了我一个错误。不知道我应该怎么写：

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

收到的错误是：

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

听起来你需要把这三个条件OR在一起，而不是把它们AND在一起。 任何行都不可能满足所有三个条件 - type不能同时具有 S、D 和 F 的值。 你可能想要

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

好吧，在三个检查条件中，您确实需要使用"or"而不是"and"。

错误消息 （ORA-02293：无法验证） 通知表室中的某些数据行违反了完整性约束。此外，还可以使用选项 VALIDATE 或 NOVALIDATE 指定表中的现有数据是否必须符合约束。如果不想验证房间中的这些现有数据行，可以使用 NOVALIDATE 指定此约束，并且 VALIDATE 是默认值。

    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

您会收到错误消息 ORA-02293，因为表中已有不满足新创建的检查约束的数据。

我认为这里的一个重要部分是要意识到您在这里没有一个业务规则要验证，而是三个。对于验证约束的用户来说，最方便的是确切地知道他插入的行出了什么问题。所以这就是为什么我会选择这些约束：

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

问候
抢。

PS：有关此主题的更多背景信息可以在我的这篇博文中找到。