Allow only positive numbers in MariaDB column

Question

I need to ensure that the numbers inserted into a column are positive (i.e. greater than zero), but could not find a way to do it. Neither decimal type's documentation nor Numeric type overview provide a solution (or a trick) that would allow me to set such a constraint on the database column.

I know there is the unsigned modifier, but it only says that the value may not be negative (i.e. less than zero) and leaves zero as a valid value for unsigned columns.

Is there a way to enforce this on the database schema level? If not, I can trivially enforce the rule in the code, but I'd like to keep type information explicit in the schema.

Most databases provide CHECK in a CREATE statement for this. MySQL or MariaDB doesnt support that feature so you must use a trigger as workaround -> https://stackoverflow.com/questions/9734920/can-a-mysql-trigger-simulate-a-check-constraint — Raymond Nijland, Aug 07 '17 at 13:25

score 2 · Accepted Answer · answered Aug 07 '17 at 15:08

MariaDB 10.2 supports CHECK constraints.

MariaDB [test]> CREATE TABLE t1 (i INT CHECK(i>0));
Query OK, 0 rows affected (1.04 sec)

MariaDB [test]> INSERT INTO t1 VALUES (1);
Query OK, 1 row affected (0.08 sec)

MariaDB [test]> INSERT INTO t1 VALUES (0);
ERROR 4025 (23000): CONSTRAINT `i` failed for `test`.`t1`
MariaDB [test]> INSERT INTO t1 VALUES (-1);
ERROR 4025 (23000): CONSTRAINT `i` failed for `test`.`t1`
MariaDB [test]> 
MariaDB [test]> SELECT @@version;
+----------------+
| @@version      |
+----------------+
| 10.2.6-MariaDB |
+----------------+
1 row in set (0.00 sec)

Allow only positive numbers in MariaDB column

1 Answers1