Search code examples
mysqlenumslinked-tables

Changing from an enum to a linked table

We are going to change something that we have an enum to an id of a linked table instead.

How would we go about that?

Our current enum name: strat
Our new linked name: stratid

What I was thinking was something along the lines of:

UPDATE table_name  
SET stratid = (SELECT id FROM link_table WHERE stratname = table_name.strat);

I have not created the link table yet, right now it is all theory.

Will the above work?
Is there anything I should change in order to transfer from an enum to the linked table?

Solution

  • No gotchas for making the update other than that you need want to make triple sure that your link_table.ids are populated strictly in the order that table_name.strat options are defined.

    For example, if strat is enum('FOO', 'BAR') then in linked_table the record with id == 1 should be the "FOO" record.

    After that, you 'd perhaps want to make the stratid column NON NULL; this is not strictly equivalent to your previous arrangement, but it would probably be closer to what you want.