[SOLVED] Removing enum values from mysql column

Removing enum values from mysql column

I have a table with a enum column called action. The permitted values currently are: act1,act2,act3,act4. I want act3 and act4 to be removed and my table's current state does not contain any rows with act3 or act4.

When I'm trying to modify the column with the new set of values it's throwing an error Data Truncated for column action.

Please suggest how do I remove the required values.

Solution

Using ALTER TABLE for adding enum values is ok and described in the MySQL documentation.

However, for removing enum values the better option is to create a new column to do the change.

ALTER TABLE your_table ADD new_action_column ENUM('act1', 'act2') ... ;
UPDATE your_table SET new_action_column = action;
ALTER TABLE your_table DROP action;
ALTER TABLE your_table CHANGE new_action_column action ENUM('act1', 'act2') ... ;

Edit

By the way. Using ENUM is not the best idea, you should use INT instead.

8 Reasons Why MySQL's ENUM Data Type Is Evil

I suggest you to use a mapping like

+------+-----+
| ENUM | INT |
+======+=====+
| act1 |  0  |
+------+-----+
| act2 |  1  |
+------+-----+