Beginner SQL: Use a foreign key for a lookup table used as an enum () field?


Say I have field Ice_Cream.flavor, with the current choices in lookup table Flavor.flavor.

I use Flavor.flavor like an enum() list, storing the value, not the record ID, in Ice_Cream.flavor. If Flavor.flavor changes, I don't want to update Ice_Cream:flavor. I want it to stay as created.

Should I set up Ice_Cream.Flavor as a foreign key, so I can see the source of the values in my ER diagram, or not?

If you want Ice_Cream.flavor to stay as created even if there is no matching record in Flavor (which is what your question sounds like) then you cannot create a FOREIGN KEY relationship, it will not allow that condition to occur in your database.

Furthermore, if you're storing the actual text Flavor.Flavor string in Ice_Cream.Flavor, there's no particular reason to have a separate RecordID column in Flavor.