How can I limit the max value of an SQL field?

I am trying to force the value of the field PRICE to be under 500 if is it paid with cash (PAYMENT = “CASH”), to make impossible to insert anything over that.

The table (SALES) is something like this:

ID (PK) [INT] | DATE [VARCHAR] | SHOP [VARCHAR] | PRICE [FLOAT] | PAYMENT [VARCHAR]

I am almost new with SQL and I have read that there are ways to do this, but I cannot find anything about it. How can I add this kind of “rules” to my table?

Answer

I believe you’re thinking of CHECK CONTRAINTS.

• https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/constraint.html

CREATE TABLE sales (
  payment   NVARCHAR2(32),
  amount    FLOAT,
  CONSTRAINT cash_limit CHECK ((payment <> 'CASH') OR (amount <= 500))
);

dbfiddle : https://dbfiddle.uk/?rdbms=oracle_21&fiddle=a1e94cdd5e762f8d7b1f8b18739cb5a0