How to add a positive integer constraint to a integer column in MySQL?

Question

How can we add a constraint which enforces a column to have only positive values.

Tried the following mysql statement but it doesn't work

create table test ( test_column integer CONSTRAINT blah > 0);

Answer 1

You would use the keyword unsigned to signify that the integer doesn't allow a "sign" (i.e. - it can only be positive):

CREATE TABLE test (
    test_column int(11) unsigned
);

You can read more about the numeric data types (signed & unsigned) here.

As far as an actual constraint to prevent the insertion-of negative values, MySQL has a CHECK clause that can be used in the CREATE TABLE statement, however, according to the documentation:

The CHECK clause is parsed but ignored by all storage engines.

For reference, here is how you would use it (and though it will execute absolutely fine, it just does nothing - as the manual states):

CREATE TABLE test (
    test_column int(11) unsigned CHECK (test_column > 0)
);

UPDATE (rejecting negative values completely)
I've noticed from a few of your comments that you want queries with negative values to be completely rejected and not set to 0 (as a normal transaction into an unsigned column would do). There is no constraint that can do this in-general (that I know of, at least), however, if you turn strict-mode on (with STRICT_TRANS_TABLES) any query that inserts a negative value into an unsigned column will fail with an error (along with any-other data-insertion errors, such as an invalid enum value).

You can test it by running the following command prior to your insert-commands:

SET @@SESSION.sql_mode = 'STRICT_TRANS_TABLES';

And if it works for you, you can either update your MySQL config with sql-mode="STRICT_TRANS_TABLES" or use SET @@GLOBAL.sql_mode = 'STRICT_TRANS_TABLES'; (I'm not sure if the SET command will affect the global mysql config though, so it may be better to update the actual config-file).

Answer 2

As of MySQL 8.0.16 (MariaDB 10.2.1), CHECK constraints are enforced. (In earlier versions constraint expressions were accepted in the syntax but ignored).

Therefore you can use:

CREATE TABLE test (
    test_column INT CHECK (test_column > 0)
);

or

CREATE TABLE test (
    test_column INT,
    CONSTRAINT test_column_positive CHECK (test_column > 0)
);