Using values from another table as ENUM values in a table

Question

Let's assume that I have mytable in mySQL

CREATE TABLE `mytable` (
  `gender` enum('MALE','FEMALE','UNISEX') NOT NULL,
);

I don't want to enumerate these values at design time. I want to put them in another_table as values.

In another_table the SELECT values are:

ID    NAME
==    ======
01    MALE
02    FEMALE
03    UNISEX

I can define the mytable.gender as INT and combine these two tables in WHERE clause with sth like mytable.gender=another_table.id.

Can I create a database level foreign-key relationship with these enum values at design time?

Guffa · Accepted Answer · 2012-06-19 21:10:52Z

2

Yes, you use a foreign key constraint.

That will make the database refuse inserts or updates to id values in the mytable table that doesn't exist in the another_table table, and refuse deletes from the another_table table for values that are used in the mytable table.

answered Jun 19, 2012 at 21:10

Guffa

703k111 gold badges760 silver badges1k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

noway Over a year ago

Thanks, you helped a lot and I could managed to get it working with making both fields as INT(4) types. What if I want to use multiple values from another_table? (MALE and FEMALE values in same row.) I couldn't change the type as SET after my initial setup :(

Guffa Over a year ago

@noway: The you would use another table to connect the two tables. You can add a unique constraint to that table, to prevent one record in mytable having more than one of each of the values from the another_table.

Collectives™ on Stack Overflow

Using values from another table as ENUM values in a table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related