Updating the MySQL ENUM Data Type

Now I am not a big fan of the ENUM data type, it I usually like VARCHAR’s or INT’s better but they do possess a certain value in constraining data to legal values.

However modifying ENUM’s are problematic. Because of several restrictions:

  • You can only append to the end of the enums.
  • You cannot delete a enum.
  • You may not reorder enums.

To modify an enum of ('WHEEL','LEG') try the following:

-- VALID CHANGE - appends to end
ALTER TABLE `ROBOT`
CHANGE COLUMN `ROBOT_LOCOMOTION` `ROBOT_LOCOMOTION`
enum('WHEEL','LEG', 'PROPELLER') DEFAULT 'WHEEL';

However these are not valid changes:

-- INVALID CHANGE - Cannot change ordering
ALTER TABLE `ROBOT`
CHANGE COLUMN `ROBOT_LOCOMOTION` `ROBOT_LOCOMOTION` 
enum('LEG', 'WHEEL', 'PROPELLER') DEFAULT 'WHEEL';

-- INVALID CHANGE - Cannot delete elements
ALTER TABLE `ROBOT`
CHANGE COLUMN `ROBOT_LOCOMOTION` `ROBOT_LOCOMOTION` 
enum('LEG') DEFAULT 'WHEEL';

George Lee

A full stack engineer who has coded on front end, back end, and mobile and embedded system. Have worked on large scale systems at eBay and agile environments at startups. Specialties: Python, PHP, Objective C, Java, Perl, SQL, Javascript, IOS, Android, Amazon EC2, Linux and Windows.

Leave a Reply

Your email address will not be published. Required fields are marked *