PostgreSQL conditional ENUM

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?

Answer

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);