What constraints to use when data modeling a subtype-supertype relationship?

Question

When modeling a supertype/subtype relationship with several mutually-exclusive subtypes, how should this mutually exclusivity be enforced by constraints?

My first guess would be:

SuperType
  (PK) super_id
  (PK) type_id
  Constraint: type_id in (1,2)

SubType1
  (FK) super_id
  (FK) type_id
  Constraint: type_id = 1


SubType2
  (FK) super_id
  (FK) type_id
  Constraint: type_id = 2

Yes, that's the way to do this. You can also have `SuperType(type_id)` as an FK to a `Types` reference table (with just 2 rows). — ypercubeᵀᴹ, Jun 24 '12 at 09:27
Another similar problem: [Database design problem](http://stackoverflow.com/questions/4969133/database-design-problem) — ypercubeᵀᴹ, Jun 24 '12 at 09:37
I agree with ypercube. You have the right idea. This is the most you can do with constraints. As Branko points out, this doesn't address widowed supertypes, but constraints (even reciprical FKs) aren't going to be a good answer for that part of the problem because of the complexity that they can cause. Just use a bit of application logic to address the widowed supertype issue. — Joel Brown, Jun 24 '12 at 12:22

score 0 · Answer 1 · edited May 23 '17 at 12:12

What you describe still doesn't enforce the presence of the child row.

For that, you'd need to ditch the type discriminator and add a FK in opposite direction, which would cause circular FKs, which would require deferred constraints to resolve - something like this.

I personally think it's better to just use the most straightforward database model possible...

SuperType
  (PK) super_id

SubType1
  (PK, FK) super_id

SubType1
  (PK, FK) super_id

...and enforce exclusivity and presence of children at the application level.

What constraints to use when data modeling a subtype-supertype relationship?

1 Answers1