CODEWITHSUNDEEP
mysqlconstraints
user462455
user462455

Reputation: 13598

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

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);

Upvotes: 20

Views: 73408

Answers (5)

Eugene Yarmash
Eugene Yarmash

Reputation: 149903

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)
);

Upvotes: 6

newfurniturey
newfurniturey

Reputation: 38446

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).

Upvotes: 27

John Woo
John Woo

Reputation: 263743

just use unsigned to allow only positive values.

CREATE TABLE hello 
(
    world int unsigned
);

uncomment the line and you will see the error saying: Data truncation: Out of range value for column 'world' at row 1:

Upvotes: 1

Mark Richman
Mark Richman

Reputation: 29710

The way to fix this is to explicitly tell MySQL Server to create an Unsigned integer. 

CREATE TABLE tbl_example ( 
example_id INT UNSIGNED NOT NULL AUTO_INCREMENT, 
example_num INT UNSIGNED NOT NULL, 
example_text TEXT 
PRIMARY KEY (`example_id`) 
);

Upvotes: 1

rharrison33
rharrison33

Reputation: 1282

You should use UNSIGNED INTEGER data type. For more information, click here

Upvotes: 0

Related Questions