MySQL Alter table causes Error: Invalid use of NULL value

My existing table:

+-----------------+---------------+------+-----+---------+-------------------+
| Field           | Type          | Null | Key | Default | Extra             |
+-----------------+---------------+------+-----+---------+-------------------+
| creation_date   | timestamp     | YES  |     | NULL                        |

I wanted to alter table like this:

ALTER TABLE enterprise MODIFY creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

But I got this error:

ERROR 1138 (22004) at line 7: Invalid use of NULL value

The problem looks like from changing the Nullable which was YES to NOT NULL. Do I need to drop the column and add afterwards?

Solution 1:

It looks like there are few rows with NULL value.Update all null values to a default date in that column and then try to do a alter.

Try this

--update null value rows
UPDATE enterprise
SET creation_date = CURRENT_TIMESTAMP
WHERE creation_date IS NULL;


ALTER TABLE enterprise 
MODIFY creation_date TIMESTAMP NOT NULL 
DEFAULT CURRENT_TIMESTAMP;

Solution 2:

You can't use this query until you have NO NULL values in the creation_date column.

Update your creation_date column with some default date and then alter the table.

Like this

UPDATE enterprise SET creation_date = CURRENT_TIMESTAMP WHERE creation_date IS NULL;

ALTER TABLE enterprise MODIFY creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;