Is it possible to set the minimum value for an integer field in the database? It is necessary to make so that at lowering the value it could not fall below zero.

  • I think that it is necessary to do the trigger. which is however not difficult at all - splash58
  • the trigger is not needed here, you just need to make the right field - strangeqargo

1 answer 1

data type int unsigned / bigint unsigned / etc.

When you try to write a number less than zero, you will throw an error, catch it or ignore it. No triggers are needed here. Those. You can, of course, make a trigger, but you are wonderful (in the sense that you can notice the execution time on a large update / insert) with your little eyes on performance.

A trigger could be needed if there is a certain minimum value, say, 10. But in fact, it is not needed here either, since you need to subtract 10 from the added value before writing to the database, at the application level.

The minimum allowable value may change (for example, some kind of VAT), and you will eventually have to follow the code and the trigger.

http://dev.mysql.com/doc/refman/5.7/en/numeric-type-overview.html

mysql> create table uns (ab int unsigned); Query OK, 0 rows affected (0.23 sec) mysql> insert into uns values(2); Query OK, 1 row affected (0.03 sec) mysql> insert into uns values(-1); ERROR 1264 (22003): Out of range value for column 'ab' at row 1 mysql> update uns set ab = -1; ERROR 1264 (22003): Out of range value for column 'ab' at row 1 mysql> update uns set ab = ab-1000; ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`db`.`uns`.`ab` - 1000)'

those. in fact, unsigned needed for additional guarantees for the consistency of data in the database and for catching errors in the code. Well, for those cases where you know that you can be sooo big, for example ID. Since the unisgned type, relatively speaking, will give two times more positive values ​​than the signed type. Therefore, I do not advise to ignore.