No, it's a sum type. You have to add the constraints
CONSTRAINT MyTableRed INTERSECT MyTableGreen IS EMPTY
CONSTRAINT MyTableGreen INTERSECT MyTableBlue IS EMPTY
CONSTRAINT MyTableBlue INTERSECT MyTableRed IS EMPTY
but after that, I believe it works. It essentially replaces the original "... and the color is 'x'" predicate with three another predicates: "... and the color is red", "... and the color is green", "... and the color is blue" with the constraint that only one (at most) of those can be true for any particular value of "...". As you can see, you have to do that for every would-be COLOR property, and adding more variants blows up the number of tables and constraints even more drastically.
It kinda reminds me of 3SAT and other logical exercises in reducing everything down to the first-order logic: it's doable, but the terms grow exponentially large.
The problem IIRC is actually consuming that sum type in SQL. Unfortunately it's been several years since I played with that approach, and I've forgotten the specific problems I encountered.
The issue I see is that now you have mixed data and schema. And unfortunately altering schema is much more difficult in current systems. There is no homoiconic SQL DBMS. This lack of homoiconicity is aso reflected in the fact that only values can be parametrized. Identifiers can not.
Also you face issues if there is some other entity depending on MyTable. You can no longer have a FK because you no longer have a table to which to point the FK, you have 3 tables.
You could create a table containing just the key and have FKs pointing from the three tables (I think this pattern does have a name) but you run more issues because there is usually no way to force bidirectional mandatory optionality using FKs. (A FK can be 0..1 - 0..M, 1..1 - 0..M, 0..1 - 0..1 or 1..1 - 0..1. But it can never be 0..1 - 1..M, 1..1 - 1..M or 1..1 - 1..1. In this case 0..1 - 1..1 is not an issue because it can be reversed as 1..1 - 0..1. )
Now you have two options:
- Either create FKs in both directions, but then you run into the issue that most DBMS can not update two tables at the same time in the same statement. You have to use transactions and defer FK checking until the end of the transaction. And by this point you have created a DSL over SQL.
- Or you use extra flag columns in order to achive a three step process (insert key in key table, insert extra attributes in other table, update key table to mark as active) and enforce everything using constraints and triggers, but similarly, by this point you have created a DSL over SQL.
It kinda reminds me of 3SAT and other logical exercises in reducing everything down to the first-order logic: it's doable, but the terms grow exponentially large.