在Oracle SQL中检查约束 [英] CHECK CONSTRAINT in Oracle SQL

问题描述

我有下表Goods_In_Wagon(Goods_ID，Wagon_ID，Total_Weight). 我需要创建一些if语句检查约束，

I have the following table Goods_In_Wagon(Goods_ID,Wagon_ID,Total_Weight). I need to create a number of if statement check constraint that says

如果WAGON_ID在90到99之间，则Total_Weight必须大于10." 和 如果WAGON_ID在100到110之间，则Total_Weight必须大于20." 和 如果WAGON_ID在111到120之间，则Total_Weight必须大于30."

"IF WAGON_ID is between 90 and 99 THEN Total_Weight must be greater than 10." AND "IF WAGON_ID is between 100 and 110 THEN Total_Weight must be greater than 20." AND "IF WAGON_ID is between 111 and 120 THEN Total_Weight must be greater than 30."

这在SQL Oracle中是否可行，如果可以实现的话

is this possible in SQL Oracle, if it is how can I implement it

推荐答案

使用离线约束:

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之间为 not ，则约束通过.如果介于90和99之间，则Total_Weight必须大于10.

If the Wagon_ID is not between 90 and 99, the constraint passes. If it is between 90 and 99, the Total_Weight must be greater than 10.

像这样的离线约束允许您在行级别应用约束逻辑，这意味着它可以使用任何列值.

An out-of-line constraint like this allows you to apply the constraint logic at the row level, meaning it can use any of the column values.

附录这是处理范围为Wagon_ID和Total_Weight的更新问题的方法.可能还有其他方法，但这感觉像最干净的"，这意味着我个人最容易阅读:)

Addendum Here's how to handle the updated question with ranges of Wagon_ID and Total_Weight. There are probably other ways but this felt like the "cleanest", meaning it was easiest for me personally to read :)

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

这篇关于在Oracle SQL中检查约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！