[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.