I am using Enum data type as a primary key referenced as a Foreign key in another table. If i have to add an extra element to this enum value, I use
ALTER TABLE <table> MODIFY <colName> ENUM(<OLD VALUES>, NEW VAL);
on both the tables. It works fine.
I had one obscure example in which I ended up going from <255 values to more. In that case, ENUM will need to switch from 1 byte storage to two bytes storage. That is when it fails. Giving me
ERROR 1025 (HY000): Error on rename of './TXCAD/#sql-5912_86' to './TXCAD/EN_TABLE' (errno: 150)
I did a some research and found out this conversion from 1 byte to two bytes in the main table makes the foreign key data type discrepancy. (If i start with 256+ values, this doesnt happen at all. I verified that)
Is there a way to force MySql to use 2 bytes from the start even if ENUM() has less than 255 values during table creation?
Using MySql - 5.1 InnoDB tables
Just so you know, I searched on this for quite a bit. From the docs, it really appears that the only way to get a 2 byte enum at table creation time is the way that you mentioned... that is, specify more than 255 items in your enum list. I don't see another part of table create syntax that allows for it. Your only other hope would be that there is a config file parameter that enables a different default.
A list of server system variables exists, but I did a quick search, and didn't see a reference to one that might change the default behavior for enum field size. http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html (note, this page is very large, and hung my browser momentarily.)
Just thought you'd like to know that someone else looked at the question and decided to investigate further, instead of just listening to crickets...