1

Let's say I have a table inventory with columns item_id, category and size.

  1. item_id is auto incremented integer (primary key)
  2. category is ENUM
  3. size is ENUM

I want size to be a conditional ENUM. By "conditional", it means for category=shoes, size has values of 5, 6, 7, 8, 35, 36, 37, 38, etc., while for category=tshirt, size has the values of S, M, L, etc.

For an item with category=shoes, its size should not be allowed as S.

What's the right way to solve this problem?

Improve this question
1
  • This feels to me like partially defeating the purpose of using enums, which is to restrict to only enum values, but to allow all these values. I can suggest that you look into check constraints. Commented Oct 3, 2018 at 5:46

2 Answers 2

Reset to default
2

You may achieve this with a complex check constraint, for instance:

ALTER TABLE inventory ADD CONSTRAINT inventory_size_check
CHECK((category = 'shoes' AND size BETWEEN '5' AND '45') OR
      (category = 'tshirt' AND size BETWEEN 'S' AND 'XXL'));

The enum size must contain all sizes for all categories.

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

5 Comments

It is a handy solution. However, my concern over this method is, it mixes data and business logic. Solution by @TomC separates data from business logic, which makes code easier to maintain.
@YikSanChan: You're right. It's not very elegant especially when the enum category tends to growing, or if the size cannot be expressed meaningfully by a value (e.g. trousers with length and waist). However, I wasn't clear from your question whether you wanted to keep the enums.
If an enum were the right thing, this would not be business logic, but data integrity, so a constraint would be the right solution. Otherwise, don't use an enum.
I think that you should use lookup tables rather than an enum, but apart from that, I favor the solution with check constraints.
I prefer @TimC 's answer as it separates data from check logic, and he uses foreign key constraint, which does similar things as explicit CHECK CONSTRAINTS. Could you please further describe why CHECK CONSTRAINTS is preferred?
1

I think the way to do this is to create additional tables such as CategorySize, which has a compound primary key on Category and Size, then use that as a foreign key in your inventory table.

That way you only allow the combinations that are valid within your inventory.

Not really enums anymore, but at least means only consistent, valid data ends up in your database.

To add the foreign key using your schema, do this:

ALTER TABLE inventory 
ADD CONSTRAINT fk_category_size 
FOREIGN KEY (category_enum,size_enum) 
REFERENCES category_size (category_enum,size_enum);
Improve this answer

5 Comments

It makes sense. However, category is used not only by size. There might be such combination like category and material, in which the solution doesn't quite scale - there are additional tables CategorySize and CategoryMaterial, but category in inventory table cannot use both as the foreign key. (Correct me if I am wrong) Then what could be the solution?
I think you would just create additional tables containing the valid combinations. Otherwise its a case of doing it in your front end application. But using tables and foreign keys will ensure valid data only gets written.
Let's say I created CategorySize and CategoryMaterial tables and they have data that represent valid combinations, see Table schemas. How to add foreign constraints? Thank you.
I've added the FK definition.
Based on @TomC 's answer, I write a working demo as proof of concept. The sql code can also be found here.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.