I have a situation where we store the value of credits a user has in the database:

`credits` DOUBLE(22,2) UNSIGNED NOT NULL DEFAULT '0.00',

The credits are numbers going from 1 to any positive value. But now there is a situation where we want to deduct credits monthly from users, and I see a situation where users may have 0 credits at the time of the SQL query.

What are the best practices to handle this? I know that allowing negative numbers can create lots of bugs and exploits but we really need to implement something this

So example: Every last day of month we will deduct -1 credit from all users. I use mariadb 10.6.11 but can upgrade to a later version if that helps

  • Illogical. If "credits are numbers going from 1 to any positive value" then DEFAULT '0.00' is at least strange. What are the best practices to handle this? Define this column as SIGNED. and add according CHECK constraint.
    – Akina
    Feb 3 at 6:23

2 Answers 2


If you wish to prevent values below a certain negative value, then you could use a check constraint:

ALTER TABLE your_table
  ADD CONSTRAINT credits_greater CHECK (credits >= -1.0);

And if you try to insert a value below, it fails the check and throws an error:

MariaDB [test]> INSERT INTO your_table(id, credits) VALUES (1, -1.1);
ERROR 4025 (23000): CONSTRAINT `credits_greater` failed for `test`.`your_table`

You could write a trigger to do something similar, or write a trigger to dynamically fix the value being inserted or updated. However, I would argue that it's best practice to use a check constraint whenever that is an option rather than using a trigger. Then let your application handle any errors thrown from the database system whenever a user attempts to violate the constraint.

Triggers have some limitations and are also seen by some as maintenance nightmares. See also MariaDB's list of trigger limitations.

  • Should I use strict mode ? It seems like a good practice to avoid bugs, and have predictable behavior unless I'm understanding it wrong, and is it possible to have it only for credits?
    – Freedo
    Feb 8 at 8:26
  • @Freedo I would definitely recommend strict mode. Otherwise you might end up with lost data (e.g. not enough space for storing a string in a varchar column) as only a warning is thrown which applications will typically ignore, so you will never notice. You can't set it just for one column, though. But sql_mode is a session variable, so it's possible to set it for only certain sessions, and then have the global sql_mode be more relaxed. Although I would recommend using strict everywhere.
    – dbdemon
    Feb 8 at 15:59
    SET foo = GREATEST(foo - 1, -10.00)

This decrements foo by 1 but prevents the result from going below -10. (Maybe this trivial code is what you are looking for?) (LEAST() is the counterpart to that.)

Do not use FLOAT or DOUBLE if you want storage and rounding to 2 decimal places. Instead, use DECIMAL(11,2) to, for example, allow values up to a billion and with 2 decimal places. (As with DOUBLE, DECIMAL is "signed".)

As for controlling limits -- that needs an application solution, not a database solution. However, see TRIGGER and/or STORED PROCEDURE as ways to encapsulate your business logic in SQL.

Every last day of month we will deduct -1 credit from all users

That UPDATE is possible, but it will be painfully slow if you have a billion users. (If you need to discuss that, write a Question focusing just on that.)

  • I don't care about the performance of the UPDATE, as it can take a few hours to run no issue. I'm just trying to avoid bugs and exploits being possible because negative values are allowed
    – Freedo
    Feb 2 at 21:22
  • And right now users do use fractions of credits... = a value of 0.5 or 1.5 or etc is allowed, i think that's why we were using double
    – Freedo
    Feb 2 at 23:49
  • Can't constraints be used instead of triggers ?
    – Freedo
    Feb 2 at 23:57
  • @Freedo - Do you want the query to fail? Or to "fix" the value?
    – Rick James
    Feb 3 at 2:22
  • 1
    @markusjm and/or dbdemon -- Provide an Answer rather than hiding in Comments.
    – Rick James
    Feb 3 at 16:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.