- Trending Categories
- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP
- Physics
- Chemistry
- Biology
- Mathematics
- English
- Economics
- Psychology
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Add a positive integer constraint to an integer column in MySQL?
You need to use unsigned for this because it won’t allow you to enter a negative number.
The syntax is as follows
CREATE TABLE yourTableName ( yourColumnName INT UNSIGNED );
To understand the concept, let us create a table. The query to create a table is as follows
mysql> create table OnlyPositiveValue - > ( - > Marks int UNSIGNED - > ); Query OK, 0 rows affected (0.58 sec)
Before inserting data in the table, use the below query.
The query is as follows
mysql> SET @@SESSION.sql_mode = 'STRICT_TRANS_TABLES'; Query OK, 0 rows affected, 1 warning (0.00 sec)
Now if you will add a negative number in the INSERT command, the following error would be visible
mysql> insert into OnlyPositiveValue values(-10); ERROR 1264 (22003): Out of range value for column 'Marks' at row 1 mysql> insert into OnlyPositiveValue values(-100); ERROR 1264 (22003): Out of range value for column 'Marks' at row 1
Let us insert positive numbers.
The query is as follows
mysql> insert into OnlyPositiveValue values(0); Query OK, 1 row affected (0.17 sec) mysql> insert into OnlyPositiveValue values(10); Query OK, 1 row affected (0.14 sec) mysql> insert into OnlyPositiveValue values(100); Query OK, 1 row affected (0.11 sec)
Display all records from the table using select statement.
The query is as follows
mysql> select *from OnlyPositiveValue;
The following is the output displaying positive numbers
+-------+ | Marks | +-------+ | 0 | | 10 | | 100 | +-------+ 3 rows in set (0.00 sec)
Advertisements