List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:September 22 1999 7:50am
Subject:Re: Creating ENUM field dynamically
View as plain text  
On Tue, 1999-09-21 13:10:58 -0400, Malaney J. Hill wrote:
> Is there a way to use a SELECT statement to grab the elements of
> an ENUM field dynamically from another table.  I have a column
> called "products" which has an ENUM field called "category_id".
> The values for "category_id" are limited to the entries in the
> "categories"
> table.  So ideally I would like to do an
> 
> category_id ENUM("select category_id from categories")
> 
> but this does not work.  Is there a way to do this or accomplish the
> same thing?

You can do it in two seperate steps, of course:
  SELECT category_id FROM categories
  --> grap results and now build new SQL:
  CREATE TABLE ... ( ..., category_id ENUM("list_of_stored_values") )

But otherwise, no, it's not possible to do it directly.
Field types are always statically defined and can't be adjusted
dynamically.  You only can change field types by the ALTER command.

For your application, the ENUM field type maybe is not the ideal type,
because for any new entry in the categories table, you'd have to ALTER
the type of category_id to match the new ENUM definition.

Better use a normal CHAR or VARCHAR field type, and check for the
appropriate values with a seperate query of the categories table!

Some other database management systems offer the FOREIGN KEY
functionality for exactly this purpose: only allowing field values
that must exist in another table.

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Thread
Creating ENUM field dynamicallyMalaney J. Hill21 Sep
  • Re: Creating ENUM field dynamicallyMartin Ramsch22 Sep