Geek Answers Handbook

Using a table to provide enum values ​​in MySQL?

Is there a way to match one of the contents of the columns of a MySQL table with an enumeration to another table in MySQL? I thought it would not be easy, but there seems to be no information I can find on this.

Any advice or help on this would be cool, and if this is not possible, does anyone know an internal reason why this is not possible?

Best wishes:)

Gary

+6
enums sql database mysql
source share
2 answers

The enum type is convenient as a one-time, but it does not scale well for multiple tables and is not standard SQL. It’s best to use regular tables and relationships here:

  • Define a new table to store a list of possible values; call him Master1
  • In the other two tables (let them be called Table1 and Table2), do not make the field an enumeration; just make this a normal field with an external key ratio to Master1.

The foreign key ratio will limit the list of possible values; and since foreign keys and relationships are completely standard SQL, this approach will have other advantages - for example, reporting tools can recognize a foreign key and understand how to use related data.

+8
source share

If it is not, do not do it.

Of course, you just need a table of possible keys, and then a foreign key mapping.

If you need a table with possible values ​​and enumeration restrictions, go to group through another table or group in the same table (if the group members are unique).

It smells like a smelly table, although USE is wise. Perhaps it is best to do this in a stored procedure or in the application code and match it with an eigenvalue?

0
source share

More articles:


All Articles