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

advertisements

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.

Related Articles

How do I create foreign keys for the junction table

I have Object1 and junction table and Object2. Object2 is table that has many junction tables, but can have only one junction table torefrencing to it. When table Object1 is removed, then junction table and Object2 should be removed. How can I make f

Same primary key as foreign key for two different tables

I want to make three tables i.e Provinces, Languages, and Cities. My Provinces and Cities use Languages. So I made join tables i.e Provinces_Languages and Cities_Languages. All tables contains id and name as columns. Provinces id name Languages id na

Several foreign keys for a single record in Rails 3?

I am working on an app that will manage students enrolled in a course. The app will have users who can log in and manipulate students. Users can also comment on students. So three of our main classes are Student, User, and Comment. The problem is tha

MySQL Errno 150 (incorrect foreign key) for CREATE TABLE

I acknowledge this has been asked several times here, and I've looked through a great many of the answers. I know that errno150 refers to a table with an incorrect foreign key, and I've seen a previous answer here. I've checked every condition there

SQL Server - Create Foreign Key on Multiple Tables

I have a requirement where I need to create a foreign key for more than 200 tables. Ex, there is emp table which has ID has primary key, now the 200 tables have emp_id, which I need to reference to emp.id. I know I can write 200 alter statements to c