MySQL enum column from anothertable column

Question

I'm sure this is either totally impossible or really easy:

If I'm creating a table and I want one of the columns to have limited options, it seems that I use either the ENUM or SET value type. But I have to define the possible values at that moment. What if I have another table which has two columns, a primary key column and a data column, and I want the ENUM for my new table to be set to the primary key of the already existing column?

I'm sure I can just write in the values long-hand, but ideally what I need is for new values to be entered into the list table and for the table with the enum column to just accept that the value choices will include anything new added to that list table.

Is this possible without needing to manipulate the structure of the new table each time something is added to the list?

Answer 1

i think this link help : http://dev.mysql.com/doc/refman/5.0/en/enum.html

have a discussion of it in the user comments start :

"In MySQL 5.0, you can convert an enum's values into a dynamically-defined table of values, which then provides effectively a language-neutral method to handle this kind of conversion (rather than relying on PHP, Tcl, C, C++, Java, etc. specific code). "

he do it with stored PROCEDURE

Answer 2

The easiest way is to use a regular column without contraints. If you're interested in all the current values, use DISTINCT to query them:

select distinct YourColumn from YourTable

That way, you don't have any maintenance and can store whatever you like in the table.

The foreign key table you mention is also a good option. The foreign key will limit the original column. Before you do the actual insert, you run a query to expand the "enum" table:

insert into EnumTable (name)
select 'NewEnumValue'
where not exists (select * from EnumTable where name = 'NewEnumValue')

Not sure what exactly you're trying to achieve btw; limit the column, but automatically expand the choices when someone breaks the limit?