Prevent negative numbers in MySQL?

MySQL MySQLi Database

To prevent negative numbers in MySQL, you need to use INT UNSIGNED. Let’s say you created a table with a column as int i.e. UserGameScores here

mysql> create table preventNegativeNumberDemo
   - > ( 
   - > UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   - > UserName varchar(20),
   - > UserGameScores int
   - > );
Query OK, 0 rows affected (1.74 sec)

Now if you need to prevent negative numbers in it, modify the same column with INT UNSIGNED

mysql> alter table preventNegativeNumberDemo modify column UserGameScores INT UNSIGNED NOT NULL;
Query OK, 0 rows affected (3.32 sec)
Records: 0 Duplicates: 0 Warnings: 0

Now check the table description once again.

The query is as follows

mysql> desc preventNegativeNumberDemo;

The following is the output

+----------------+------------------+------+-----+---------+----------------+
| Field          | Type             | Null | Key | Default | Extra          |
+----------------+------------------+------+-----+---------+----------------+
| UserId         | int(11)          | NO   | PRI | NULL    | auto_increment |
| UserName       | varchar(20)      | YES  |     | NULL    |                |
| UserGameScores | int(10) unsigned | NO   |     | NULL    |                |
+----------------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

Now if you try to insert negative number for the UserGameScores column then MySQL gives an error since we have set it as INT UNSIGNED. Let us insert some values including negative number

mysql> insert into preventNegativeNumberDemo(UserName,UserGameScores) values('Larry',0);
Query OK, 1 row affected (1.20 sec)
mysql> insert into preventNegativeNumberDemo(UserName,UserGameScores) values('Mike',-1);
ERROR 1264 (22003): Out of range value for column 'UserGameScores' at row 1
mysql> insert into preventNegativeNumberDemo(UserName,UserGameScores) values('Sam',-100);
ERROR 1264 (22003): Out of range value for column 'UserGameScores' at row 1
mysql> insert into preventNegativeNumberDemo(UserName,UserGameScores) values('John',100);
Query OK, 1 row affected (0.84 sec)
mysql> insert into preventNegativeNumberDemo(UserName,UserGameScores) values('Bob',200);
Query OK, 1 row affected (0.48 sec)

Look at the above error when we try to insert negative value.

Now display all records from the table using select statement. Only the positive numbers will get inserted

mysql> select *from preventNegativeNumberDemo;

The following is the output

+--------+----------+----------------+
| UserId | UserName | UserGameScores |
+--------+----------+----------------+
|      1 | Larry    |              0 |
|      2 | John     |            100 |
|      3 | Bob      |            200 |
+--------+----------+----------------+
3 rows in set (0.00 sec)

Arjun Thakur

Updated on: 30-Jul-2019

2K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started