RAISE OR PRINT ERROR on Terminal if the condition is not met during UPDATE query

Question

I am working on MARIADB terminal. I have a task to write query that deducts the quantity of a product by 5, before it updates, it needs to check that we have sufficient amount of quantity. If the quantity goes below 0 then we dont want to udpate, instead, we would like to throw an error.

This is not a pl/sql or procedure. Just a query that we can execute directly on terminal/mysql shell

Query written so far:

Update inventory 
 SET 
 quantity_in_stock = 
 IF(((SELECT quantity_in_stock from inventory WHERE product_id = 101)-5) >= 0,
 ((SELECT quantity_in_stock from inventory where product_id =101)-5),
 ()
 where product_id = 101;

I want to raise ERROR in the () section (2nd last line of query).

SQL-MARIADB statement to raise/generate an error in () section of update query.

Answer 1

If possible, Data integrity compliance should be done on the server side, not by the application.

In order to prevent, for example, that a certain article is ordered that is not in sufficient quantities in stock, it must be ensured that the stock cannot be negative. This needs to be specified in the definition of the table.

If you define quantities_in_stock as an unsigned integer, it can't be < 0.

Example:

CREATE TABLE inventory(product_id INT UNSIGNED NOT NULL, quantity INT UNSIGNED);
INSERT INTO inventory VALUES (1, 3);
UPDATE inventory SET quantity=quantity - 5 WHERE product_id=1;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '`test`.`inventory`.`quantity` - 5'

If you can't modify the table definition, you can create a trigger. Another option to ensure data integrity is the check constraint.