Should I use an ENUM for primary and foreign keys?

Question

An associate has created a schema that uses an ENUM() column for the primary key on a lookup table. The table turns a product code "FB" into it's name "Foo Bar".

This primary key is then used as a foreign key elsewhere. And at the moment, the FK is also an ENUM().

I think this is not a good idea. This means that to join these two tables, we end up with four lookups. The two tables, plus the two ENUM(). Am I correct?

I'd prefer to have the FKs be CHAR(2) to reduce the lookups. I'd also prefer that the PKs were also CHAR(2) to reduce it completely.

The benefit of the ENUM()s is to get constraints on the values. I wish there was something like: CHAR(2) ALLOW('FB', 'AB', 'CD') that we could use for both the PK and FK columns.

What is:

1. Best Practice
2. Your preference

This concept is used elsewhere too. What if the ENUM()'s values are longer? ENUM('Ding, dong, dell', 'Baa baa black sheep'). Now the ENUM() is useful from a space point-of-view. Should I only care about this if there are several million rows using the values? In which case, the ENUM() saves storage space.

Celanese

Answer 1

ENUM should be used to define a possible range of values for a given field. This also implies that you may have multiple rows which have the same value for this perticular field.

I would not recommend using an ENUM for a primary key type of foreign key type.

Using an ENUM for a primary key means that adding a new key would involve modifying the table since the ENUM has to be modified before you can insert a new key.

I am guessing that your associate is trying to limit who can insert a new row and that number of rows is limited. I think that this should be achieved through proper permission settings either at the database level or at the application and not through using an ENUM for the primary key.

IMHO, using an ENUM for the primary key type violates the KISS principle.

Misdeem

+1 an enum is by definition a limited set, and a table is by definition an unbounded set – Thriftless

Answer 2

but when you only trapped with differently 10 or less rows that wont be a problem

e.g's

CREATE TABLE `grade`(
    `grade` ENUM('A','B','C','D','E','F') PRIMARY KEY,
    `description` VARCHAR(50) NOT NULL
) 

This table it is more than diffecult to get a DML

Mover

Answer 3

We've had more discussion about it and here's what we've come up with:

Use CHAR(2) everywhere. For both the PK and FK. Then use mysql's foreign key constraints to disallow creating an FK to a row that doesn't exist in the lookup table.

That way, given the lookup table is L, and two referring tables X and Y, we can join X to Y without any looking up of ENUM()s or table L and can know with certainty that there's a row in L if (when) we need it.

I'm still interested in comments and other thoughts.

Celanese

Answer 4

Having a lookup table and a enum means you are changing values in two places all the time. Funny... We spent to many years using enums causing issues where we need to recompile to add values. In recent years, we have moved away from enums in many situations an using the values in our lookup tables. The biggest value I like about lookup tables is that you add or change values without needing to compile. Even with millions of rows I would stick to the lookup tables and just be intelligent in your database design

Fieldsman

Answer 5

A strong reason to not use enums as primary keys: there's no way to rename or drop an enum element. You have no option other than creating an entirely new enum and dropping the old one -- barf.

You would (1) create a new enum with the desired changes; (2) migrate existing data to the new type by either (2a) dropping the FK constraint to the old enum on the current column, mapping the values, and adding a new FK constraint to the new enum on the same column, or (2b) creating a parallel FK column to the new enum, mapping the current column values into the new column, and dropping the old column.

Very messy. Much easier to create a small table that's used like an enum (aka a code table).

Exempt