How to alter MySQL table without losing data?
Your question is quite obvious. You're adding a new column to the table, and setting it to NOT NULL.
To make things clearer, I will explain the reaction of the server when you run the command:
You add a new column, so every row of the table has to set a value for that column.
As you don't declare any default value, all the rows set
nullfor this new column.The server notices that the rows of the table have a
nullvalue on a column that doesn't allownulls. This is illegal.To solve the conflict, the invalid rows are deleted.
There are some good fixes for this issue:
Set a default value (recommended) for the column you're creating.
Create the column without the
NOT NULL, set the appropiate values, and then make the columnNOT NULL.