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;

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

Answer 3

simply,we can't change table structure if it is full with data. Solving is:

1)delete all data in it by(delete from table_name) or update it by (update table_name set new_value where condition )

2)use (alter with modify or change ) to renew the structure of table