Search code examples
mysqlsqldatabasealter

Adding a column requires a default value for another column?

I'm adding a column to a table, but it tells me I'm not setting a default value for another column of the table;

ALTER TABLE `myDatabase`.`myTable` ADD myNewColumn varchar(2);

Gives me the response

ERROR 1067 (42000): Invalid default value for 'modifiedDate'

I don't get why I need to set a default value for a column when I'm just altering a table?

Solution

  • The problem was with my table definition, not the command itself.

    The table had modifiedDate set incorrectly I assume, I changed it to use CURRENT_TIMESTAMP instead with the following command

    ALTER TABLE myTable MODIFY column modifiedDate timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP;

    After this everything worked. Below is the description of this row before:

    | Field | Type | Null| Key | Default
    | modifiedDate | timestamp | NO | | 0000-00-00 00:00:00

    And after :

    | Field | Type | Null| Key | Default
    | modifiedDate | timestamp | NO | | CURRENT_TIMESTAMP