MySQL Alter table causes Error: Invalid use of NULL value

Question

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?

Answer 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;