Enforcing supertype & subtype data integrity without stored procedures, triggers, or UDFs
Unfortuntately, "modern" DBMSes don't directly support all of the fancy symbols1 that you can put into an ER diagram. All that the physical FOREIGN KEY actually enforces is that the child row cannot exist without parent, which gets you a plain "1 to 0 or N" relationship2.
You can...
- make FK NULL-able to morph the left side of the relationship to "0 or 1"
- and/or you can put a key on top of FK to morph the right side to "0 or 1"
...but that's about all you can do "out of box".
To enforce other rules3, you'll either have to significantly "uglify" the model and probably employ deferred constraints4, or you can do it do it in procedural code5.
While your instincts about putting as much as possible of the integrity rules in the database itself are spot on, it is nonetheless considered a lesser evil to just enforce "unusual" cases in procedural code instead of twisting the data model into a pretzel to accommodate the limitations of declarative constraints.
In fact, one of the most popular techniques is to create an "API":
- forbid the clients from modifying the tables directly (by revoking the appropriate permissions)
- and allow them to only modify the data through stored procedures that you wrote and which enforce all the necessary business rules6. That way, you funnel all the clients through the same "clearing house" and nobody can misbehave.
That's a rather "heavy-duty" solution though, and may not be worth the trouble if the scenario is simple enough. If your application is the only one that is ever going to modify the database, then just implementing the rules in the client code may be enough...
1 Such as inheritance (aka. subtype, category, generalization hierarchy).
2 Left side: any given child must have "1" parent. Right side: any given parent must have "0 or N" children.
3 Such as exclusivity and presence of children, which is important for inheritance, as you already noted.
4 Which are supported by PostgreSQL but not MySQL.
5 In order of preference:
- triggers and stored procedures
- middle-tier
- or client.
6 But be wary of race conditions: transaction isolation will protect you from some of them, but nut not all, and you may need to do some explicit locking.