MySQL 5.7 change a datetime column to default NULL from 0000-00-00 00:00:00

by ·

So I started working on one of my Laravel projects, due to its large size, I was not able to perform migrations. In this case, we decided to directly export the MySQL database from the live server to our local server.

During this process, we faced the below errors.

  1. Not able to update the date column field in MySQL to default NULL.
  2. SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘0’ for key ‘PRIMARY’

I faced the issue of updating the Table column of type Date/Time to NULL. There were 100’s of rows in the table. I have to update the column value to NULL in it.

If there were 10-20 rows in the table I can update that manually as well. But, as the number of rows is big. So, I found the below way to simplify the changes. Adding it here so you can do that too.

How to change a datetime or date column in MySQL to default NULL.?

I found on the internet we can do it by NO_ZERO_DATE compatibility mode as well, BUT what if we don’t want to go for it.

Without using the NO_ZERO_DATE compatibility mode, We can also fixe the incorrect datetime value: ‘0000-00-00 00:00:00’.

/** ---
* To set an existing column in MySQL 5.7 from 0000-00-00 00:00:00 to NULL as default
* We will be following 3 iterations to get this task done.
--- */

/* Step 1) Set all 0000-00-00... to a valid but a date which is rarely used i.e 5001-01-01 value: */
UPDATE my_table SET my_datetime_col = '5001-01-01 00:00:00' WHERE CAST(my_datetime_col AS CHAR(20)) = '0000-00-00 00:00:00';

I am casting CHAR(20) because I have both values i.e date and time in my database table column.

/* Step 2) Modify the column to DEFAULT: NULL */
ALTER TABLE my_table MODIFY COLUMN my_datetime_col DATETIME NULL;

/* Step 3) Revert the valid rarely used value to NULL */
UPDATE my_table SET my_datetime_col = NULL WHERE my_datetime_col = '5001-01-01 00:00:00';

For Date Columns only – just changed the CAST(my_date_col AS CHAR(20))  to CAST(my_date_col AS CHAR(10))

/** ---
* For DATE Columns
*/
/* Step 1) Set all 0000-00-00... to a valid but a date which is rarely used i.e  5001-01-01 value: */
UPDATE my_table SET my_date_col = '5001-01-01' WHERE CAST(my_date_col AS CHAR(10)) = '0000-00-00';

/* Step 2) Modify the column to DEFAULT: NULL */
ALTER TABLE my_table MODIFY COLUMN my_date_col DATE NULL;

/* Step 3) Revert the valid nonsense value to NULL */
UPDATE my_table SET my_date_col = NULL WHERE my_date_col = '1001-01-01';

2. SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘0’ for key ‘PRIMARY’

This case arise when we exported the MySQL database directly from live server to our local database. As, the database is large, i was not able to do the migrations.

Error : SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘0’ for key ‘PRIMARY’.  

Cause: This issue arose because while exporting from the live server Primary key with AUTO_INCREMENT clause was not exported correctly with the tables. 

Solution: We should add the AUTO_INCREMENT clause to the PRIMARY KEY of the table. Below is the syntax for the same

Syntax is

ALTER TABLE `TABLE_NAME`
CHANGE COLUMN `COLUMN_NAME` `COLUMN_NAME` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;

So, in the below manner, we resolved the issue.

ALTER TABLE `test_has_selected_questions`
CHANGE COLUMN `id` `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;

Note: If there is any row with the id value “0”. Please delete the row first, then go for running the above command.

Read more on MySQL Interview Questions and Answers!

 

You may also like...