CHECK CONSTRAINT in Oracle SQL

Question

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

"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."

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

Answer 1

Use an out-of-line constraint:

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

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.

---

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