在Oracle SQL中检查约束条件

Question

我有下表Goods_In_Wagon（Goods_ID，Wagon_ID，Total_Weight）。 我需要创建一些if语句检查约束，它说

“如果WAGON_ID介于90和99之间，那么Total_Weight必须大于10”。 “ AND ”如果WAGON_ID在100和110之间，那么Total_Weight必须大于20.“ “ AND ”如果WAGON_ID在111和120之间，那么Total_Weight必须大于30.“

在SQL甲骨文这是可能的，如果是我怎么能实现它

Answer 1

使用超出线的约束：

CREATE TABLE Goods_In_Wagon (
    Goods_ID NUMBER(whatever), 
    Wagon_ID NUMBER(whatever), 
    Total_Weight NUMBER(whatever), 
    CONSTRAINT Check_WagID_Weight 
    CHECK (Wagon_ID NOT BETWEEN 90 AND 99 OR Total_Weight > 10) 
) 

如果Wagon_ID是90和99之间不 ，约束通过。如果它在90到99之间，那么Total_Weight必须大于10.

这样的一个外部约束允许您在行级应用约束逻辑，这意味着它可以使用任何列值。

---

附录下面是如何处理与Wagon_ID和Total_Weight范围内更新的问题。可能有其他的方法，但这种感觉就像“最干净”，这意味着它是最简单的对我个人阅读:)

CREATE TABLE Goods_In_Wagon(
    Goods_ID NUMBER(whatever), 
    Wagon_ID NUMBER(whatever), 
    Total_Weight NUMBER(whatever), 
    CONSTRAINT Check_WagID_Weight 
    CHECK (
     (Wagon_ID < 90) OR 
     (Wagon_ID BETWEEN 90 AND 99 AND Total_Weight > 10) OR 
     (Wagon_ID BETWEEN 100 AND 110 AND Total_Weight > 20) OR 
     (Wagon_ID BETWEEN 111 AND 120 AND Total_Weight > 30) OR 
     (Wagon_ID > 120) 
    ) 
) 

Answer 2

修订，以反映附加要求

CREATE TABLE Goods_In_Wagon 
(
    Goods_ID 
    ,Wagon_ID 
    ,Total_Weight 
    CONSTRAINT check_Weight 
    CHECK ( 
     WAGON_ID < 90 
     OR 
     (TOTAL_WEIGHT > 10 AND WAGON_ID >= 90 AND WAGON_ID <= 99) 
     OR 
     (TOTAL_WEIGHT > 20 AND WAGON_ID >= 100 AND WAGON_ID <= 110) 
     OR 
     (TOTAL_WEIGHT > 30 AND WAGON_ID >= 111 AND WAGON_ID <= 120) 
     OR 
     WAGON_ID > 120 
    ) 
)