Altering an ENUM or SET data type in MySQL

[curtiss]

Greetings!
I am currently working on a database design, and would like to make use of the ENUM and SET data types to make my life a little easier (in my old Access database, I have each of the sets stored in a separate table, and refer to them by their primary key).

However, I have come across one problem that makes me weary about using the ENUM and SET datatypes.

Let's say I have a column that should allow the following values:

• Employee
• Administrator
• Client

Now, let's say that I set that column as an ENUM with 'Employee','Administrator','Client' as the available values.

If something happens that makes it necessary to change the name of one of those items, my table data is going to get truncated.

For instance, let's say that the company decides that "Administrator" is no longer politically correct, and they want to change it to "Executive". If I use the following query to change my column definition:

PHP Code:

UPDATE TABLE contact MODIFY COLUMN typeofrecord ENUM('Employee','Executive','Client'); 


Every record that had "Administator" set as its typeofrecord will now be null, because the 'Administrator' value no longer exists.

Is there a way to alter the labels associated with ENUM and SET data options without it blanking out all records that had that label?

I realize, in the example I showed, that I could probably just execute an update query that sets all "null" fields to 'Executive'. However, that will only work if I didn't have any null fields before I performed the table alteration.

Any advice would be greatly appreciated. It seems ridiculous to create a whole separate table just to store the following data:

ID     Type
-----------------------
1     Employee
2     Administrator
3     Client

However, if there is no way to safely update an ENUM's labels, then that is what I will have to do.

Thanks in advance for any help you can offer.

[zoul]

I would suggest doing this:

Alter the table, add an ADDITIONAL enum value
Update the table, set all 'Administrator' to 'Executive'
Alter the table, remove the 'Administrator' enum value

Although, personally ... I would use the simple 'EmployeeType' table and do a join -- and, if the values 'Executive', 'Administrator', etc ... are just that, 'Lookup Values' -- then simply store all your lookup values in a single table, not one table for each one.

create table EnumValues (
EnumValueId int,
EnumValueGroup varchar(255),
EnumValueField varchar(255),
EnumValueFieldId varchar(255)
);

Then simply store a reference to 'EnumValueId' in the Employee table, and populate the 'EnumValues' table like so:

insert into EnumValues(EnumValueGroup, EnumValueField, EnumValueFieldId)
values('Employee Type', 'Executive', 0);

You can use the 'EnumValueId' in your application code to match to an actual Enum or low-number constant and use the EnumValueId to keep your referential integrity in the database.


The initial idea would solve your 'null value' issue -- especially if the field your updating allows nulls to begin with.

-- MoZ

[curtiss]

Thank you, Zoul.

I think I'm actually going to go with your second suggestion, as I hadn't really thought of that.