CODEWITHSUNDEEP
sqlentity-frameworkdatabase-designef-code-first
RobVious
RobVious

Reputation: 12925

TPH - how to satisfy FK constraint when FK is on derived class?

Let's say I have TPH for abstract class Person. Then I have Girl and Boy derived from this class. Girl has a relationship with FavoriteMakeup that Boy does not have. How do I satisfy the FK constraint on Makeup when inserting a Boy record? Or is TPH incompatible with FKs limited to derived types?

TPH: Table Per Hierarchy
FK: Foreign Key

Upvotes: 0

Views: 99

Answers (1)

Renzo
Renzo

Reputation: 27424

There are two separate aspects here. Let's examine both of them.

The first is if a foreign key can have or not null values in a Relational DBMS. In general this is allowed: you can have both a foreign key constraint and a not null constraint or a foreign key constraint while the attribute can be, at the same time, nullable. This is because the two constraints are usually considered independent. For instance, a NULL value could have the meaning that the value is unknown for a particular object.

The second aspect is related to modeling: usually, in cases like your example, while you can use the “Table per Hierarchy” approach, this is not considered a good modeling practice: you should use the “Table per Type” (or TPT to use the jargon of Microsoft documentation) approach, since all the Girl entities have an association with the other entities Makeup, while the entities Boy do not have this association, and this fact is part of the meaning of the entity, not some special case for some entity (like an unknown value).

Upvotes: 1

Related Questions