MySQL Alter table causes Error: Invalid use of NULL value
MysqlMysql Problem Overview
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?
Mysql Solutions
Solution 1 - Mysql
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 - Mysql
> 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;
Solution 3 - Mysql
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