MySQL Non-Negative INT Columns

Question

I am coding and ran into a simple problem that I am sure there is a solution too - I just haven't been able to find it. I want to do the following query:

UPDATE `users` SET balance = (balance - 10) WHERE id=1

But if the balance will become a negative number I want an error to be returned. Any ideas on if this is possible?

Thank you,

Ice

Answer 1

If you do

UPDATE `users` SET balance = (balance - 10) WHERE id=1 and balance >=10

You should be able to detect that a row was not modified.

Note that while another answer suggests using an unsigned int column, this may not work:

Create a test table

create table foo(val int unsigned default '0');
insert into foo(val) values(5);

Now we attempt to subtract 10 from our test row:

update foo set val=val-10;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> select * from foo;
+------------+
| val        |
+------------+
| 4294967295 |
+------------+

This was on mysql 5.0.38

Answer 2

This sort of things is done by triggers. MySql have support for triggers only since 5.0.2.

DELIMITER $$
CREATE TRIGGER balance_check BEFORE INSERT ON user FOR EACH ROW
BEGIN
    IF new.balance < @limit_value THEN
        -- do something that causes error. 
        -- mysql doesn't have mechanism to block action by itself
    END IF;
END $$
DELIMITER ;

Triggers in MySql are quite rudimentary. You have to hack things around to do some things (e.g. cause error).

Answer 3

I dont think you can do this with a simple query. you should use a mysql user defined function that manage that before update the row. or a trigger

Answer 4

You can make the balance field of the users table an unsigned int:

ALTER TABLE `users` CHANGE `balance` `balance` INT UNSIGNED;