MySQL Non-Negative INT Columns

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

From stackoverflow

• 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

  enobrev : Odd that the error is inconsistent. I'm getting it on 5.0.45

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

• 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

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

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

  enobrev : You beat me to it by a few seconds. MySQL will return a "#1264 - Out of range value adjusted for column 'balance'" error for negative numbers on an unsigned field

  Paul Dixon : I didn't get that result: create table tmp2 (val int unsigned default '0'); insert into tmp2(val)values(5); update tmp2 set val=val-10; select * from tmp2; +------------+ | val | +------------+ | 4294967295 | +------------+

  Paul Dixon : hmm, difficult to read! suffice to say, when I subtract 10 from an unsigned int column containing 5, I get no error and a new value of 4294967295 on mysql 5.0.38

  Slartibartfast : and if business logic changes to allow $100 debt, you are back at square one.

  Brian Fisher : On my local instance of MySQL 5.0.67 I get a #1264 warning and it sets the value to 0.

  rikh : This can result in the value wrapping around to a massive positive value. If this is dealing with money, that would be baaaad.