Why Am I Getting an 'Invalid Default Value' Error When Adding a Text Column to My Table?

Invalid Default Value Error in Table Alteration

When attempting to modify your table by adding a new column, you encountered an error: "ERROR 1067 (42000): Invalid default value for 'created_at'". Despite googling, you discovered that this error typically arises during timestamp alteration. However, your situation involves adding a new text column named "bank."

To resolve this issue, you must verify your sql_modes. Use the following command:

show variables like 'sql_mode' ; 

If you notice the presence of "NO_ZERO_IN_DATE,NO_ZERO_DATE" in your sql_modes, this is causing the problem. Disable these modes to allow your query to succeed.

To globally modify the sql_mode as a root user, execute the following command:

set global sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

Once you have disabled the problematic sql_modes, you should be able to successfully add the "bank" column to your investments table.

The above is the detailed content of Why Am I Getting an 'Invalid Default Value' Error When Adding a Text Column to My Table?. For more information, please follow other related articles on the PHP Chinese website!