Set minimum value of MySQL field - possible?

Question

Quick newbie MySQL question. What would be the simplest way to ensure that the minimum value of a given field is 0?

Basically, we have a script that runs automatically and subtracts an integer value from the value of a field every 15 minutes--but we want any entry that gets to 0 to stay at 0 and not go negative.

This could be simply done with a loop in PHP but I'm trying to limit calls to the database...

Is there a simple way to either make the minimum value for a field 0 or make it so any negative value put in that field automatically becomes 0?

Thanks!

Answer 1

you can set UNSIGNED attribute for your field. mysql will generate an error if you try to set this field to something < 0.

Answer 2

Have you tried a simple conditional "WHERE" or is the problem more complex than that?

UPDATE mytable SET mycolumn=mycolumn-1 WHERE mycolumn>0

Answer 3

You could add the logic to your script to not subtract from the value if the value is already zero...

An update trigger that corrects negative values, setting them to zero is another idea.

Answer 4

Common way to do this would be CHECK constraint but MySQL does not support that.

So either make the column unsigned and/or make sure that in your application logic you are subtracting the value only when it is > 0.