By: Eduardo Ponce de Leon
2019-11-08


Web Development Blog / MySQL /

Error: Column cannot be Null, How do I modify a MySQL column to allow NULL?

By: Eduardo Ponce de Leon
2019-11-08

Error: Column cannot be Null, How do I modify a MySQL column to allow NULL?
Solved: This happens because when inserting an new row and some columns are not mentioned in the INSERT query, they are filled up as NULL by default, or maybe you are actually inserting a value of NULL, but for this to work your column must be set to allow NULL, other wise it will crash,

 

Solved: Error: Column column cannot be null : UPDATE TABLE SET COLUMN = NULL

 

If you have encountered this problem most probably your MySQL version updated and Strict Mode is enabled. Read more about it here: Strict Mode enabled by default

 

This happens because when inserting an new row and some columns are not mentioned in the INSERT query, they are filled up as NULL by default, or maybe you are actually inserting a value of NULL, but for this to work your column must be set to allow NULL, other wise it will crash,

 

To fix this you have a few options. Using PHPMYADMIN you can go to the table, Clik on Structure and the under ACTIONS edit the column that is giving you trouble.

 

Then select NULL and save:

Or if you prefer coding it directly on a query add the following:

ALTER TABLE table_name CHANGE column_name column_name type DEFAULT NULL

for exmaple:

ALTER TABLE STUDENTS CHANGE registration_date registration_date  DATETIME DEFAULT NULL;

 

 

I hope this helps, 

Good Luck!

 

 

Comments