mysql prevent negative numbers

Question

Is their an int field in mysql where negative numbers are not allowed? or more specifically if a negative number is inserted into the field it will insert a zero. I ask this because we have a scoring system and we don't allow people to have negative scores. So if their score does reach bellow zero, it will just insert a zero instead. I'm trying to do this without having to query the user's score to check if it will fall bellow zero.

Answer 1

In addition to the DDL change (INT UNSIGNED) that others have recommended, I'd also change your application logic. You say:

I'm trying to do this without having to query the user's score to check if it will fall bellow zero.

You don't have to explicitly check in a separate query:

UPDATE your_table
   SET score = GREATEST(score + ?, 0) -- This '?' is the adjustment to the score
 WHERE user_id = ?

Now your application cannot UPDATE score to fall below zero, nor will it generate errors or warnings depending on the SQL mode.

Answer 2

Yes. You can create an int field and mark it as UNSIGNED.

From MySQL 5.0 Reference Manual:

INT[(M)] [UNSIGNED] [ZEROFILL] 

 A normal-size integer. The signed range is -2147483648 to 2147483647. 
 The unsigned range is 0 to 4294967295.

Answer 3

MySQL has an UNSIGNED qualifier for integer types.

Negative values will be clamped to zero, but will generate a warning:

mysql> create table test ( id int(5) unsigned not null );
Query OK, 0 rows affected (0.05 sec)

mysql> insert into test values (-1), (5), (10);
Query OK, 3 rows affected, 1 warning (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 1

mysql> select * from test;
+----+
| id |
+----+
|  0 |
|  5 |
| 10 |
+----+
3 rows in set (0.01 sec)

Answer 4

If you are running in strict sql mode this would throw an error and an insert/update would fail.

I usally create a user-defined function for this sort of thing. (In this case a very trivial "if (expr1, expr2, expr3)" will do the trick