CODEWITHSUNDEEP
mysql
Ice
Ice

Reputation:

MySQL Non-Negative INT Columns

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?

Upvotes: 4

Views: 4469

Answers (5)

Jay Brunet
Jay Brunet

Reputation: 3750

Just a tip that wouldn't fit as a comment. I was just trying to subtract 32000 from 32047 (not a negative result) and was getting errors. Also confusing, I was getting BIGINT errors but my subtraction was on a SMALLINT column! (Which still makes no sense.)

If you're getting "out of range" errors even when your "balance" is positive, try adding "limit 1" to the end of your query. Maybe this is a bug in MySQL? 

mysql> update posts set cat_id=cat_id-32000 where timestamp=1360870280;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`xxxxx`.`posts`.`cat_id` - 32000)'

mysql> update posts set cat_id=cat_id-32000 where timestamp=1360870280 limit 1; 
Query OK, 1 row affected (6.45 sec)
Rows matched: 1  Changed: 1  Warnings: 0

In my case the timestamp is unique (I just checked to be sure) but not explicitly defined as unique when I created the table. So why is the "limit 1" here necessary? But who cares, it works!

Upvotes: 0

Slartibartfast
Slartibartfast

Reputation: 8805

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

Upvotes: 1

Paul Dixon
Paul Dixon

Reputation: 300855

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

Upvotes: 6

Brian Fisher
Brian Fisher

Reputation: 23989

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

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

Upvotes: 5

Gabriel Sosa
Gabriel Sosa

Reputation: 7956

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

Upvotes: 0

Related Questions