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

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

Solution 2

Revised to reflect the additional requirements

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

Comments

[a1ex07]

+1. But I believe constraint should be modified to deal with NULLs , for instance ` ... OR NVL( Total_Weight ,0) >10)`

[Ed Gibbs]

@a1ex07 - good call. user2154840 - do you have any special requirements for dealing with null values? If so that changes things a bit. As for your edit: no problem. Oracle can handle that. I'll post an update shortly.

[Declan_K]

OK, the contstraint above now check for each condition on a seperate line.

[Ed Gibbs]

user2154840 - I've added the CHECK for the updated question as an addendum to my answer. As @a1ex07 pointed out, if you have to handle null weights it changes things slightly. Let me know if that's the case and I'll make an update.