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
How it is possible to insert a zero or an empty string into a MySQL column which is defined as NOT NULL?
Declaring a column ‘NOT NULL’ means that this column would not accept NULL values but zero (0) and an empty string is itself a value. Hence there would be no issue if we want to insert zero or an empty string into a MySQL column which is defined as NOT NULL. Following comparisons of 0 and empty string with NULL would make it clear −
mysql> Select 0 IS NULL, 0 IS NOT NULL; +-----------+---------------+ | 0 IS NULL | 0 IS NOT NULL | +-----------+---------------+ | 0 | 1 | +-----------+---------------+ 1 row in set (0.00 sec)
The above result set shows that zero (0) is not NULL. It means zero (0) is a value itself because as we know that NULL means NO VALUE.
mysql> Select '' IS NULL, '' IS NOT NULL; +------------+----------------+ | '' IS NULL | '' IS NOT NULL | +------------+----------------+ | 0 | 1 | +------------+----------------+ 1 row in set (0.00 sec)
The above result set shows that empty string (‘’) is not NULL. It means empty string (‘’) is a value itself because as we know that NULL means NO VALUE.
Example
mysql> create table test(id int NOT NULL, Name Varchar(10));
Query OK, 0 rows affected (0.19 sec)
mysql> Insert into test6(id, name) values('1', 'Gaurav'),('0','Rahul'),('','Aarav');
Query OK, 3 rows affected, 1 warning (0.08 sec)
Records: 3 Duplicates: 0 Warnings: 1
Warning (Code 1366): Incorrect integer value: '' for column 'id' at row 3
mysql> Select * from test;
+----+--------+
| id | Name |
+----+--------+
| 1 | Gaurav |
| 0 | Rahul |
| 0 | Aarav |
+----+--------+
3 rows in set (0.00 sec)From the result sets above, it can be observed that we can insert zero(0) an empty string(‘’) into a column which is declared as NOT NULL.
1K+ Views
