SQL sub-types with overlapping child tables

Question

Consider the problem above where the 'CommonChild' entity can be a child of either sub-type A or B, but not C. How would I go about designing the physical model in a relational [SQL] database?

Ideally, the solution would allow...

1. for an identifying relationship between CommonChild and it's related sub-type.
2. a 1:N relationship.
   

Possible Solutions

1. Add an additional sub-type to the super-type and move sub-type A and B under the new sub-type. The CommonChild can then have a FK constraint on the newly created sub-type. Works for the above, but not if an additional entity is added which can have a relationship with sub-type A and C, but not B.

2. Add a FK constraint between the CommonChild and SuperType. Use a trigger or check constraint (w/ UDF) against the super-type's discriminator before allowing a new tuple into CommonChild. Seems straight forward, but now CommonChild almost seems like new subtype itself (which it is not).

3. My model is fundamentally flawed. Remodel and the problem should go away.

I'm looking for other possible solutions or confirmation of one of the above solutions I've already proposed.

Thanks!

---

EDIT

I'm going to implement the exclusive foreign key solution provided by Branko Dimitrijevic (see accepted answer).

I am going to make a slight modifications in this case as:

1. the super-type, sub-type, and "CommonChild" all have the same PKs and;
2. the PKs are 3 column composites.

The modification is to to create an intermediate table whose sole role is to enforce the exclusive FK constraint between the sub-types and the "CommonChild" (exact model provided by Dimitrijevic minus the "CommonChild's" attributes.). The CommonChild's PK will have a normal FK constraint to the intermediate table.

This will prevent the "CommonChild" from having 2 sets of 3 column composite FKs. Plus, since the identifying relationship is maintained from super-type to "CommonChild", [read] queries can effectively ignore the intermediate table altogether.

Answer 1

Looks like you need a variation of exclusive foreign keys:

CREATE TABLE CommonChild (
    Id AS COALESCE(SubTypeAId, SubTypeBId) PERSISTED PRIMARY KEY,
    SubTypeAId int REFERENCES SubTypeA (SuperId),
    SubTypeBId int REFERENCES SubTypeB (SuperId),
    Attr6 varchar,
    CHECK (
        (SubTypeAId IS NOT NULL AND SubTypeBId IS NULL)
        OR (SubTypeAId IS NULL AND SubTypeBId IS NOT NULL)
    )
);

There are couple of thing to note here:

• There are two NULL-able FOREIGN KEYs.
• There is a CHECK that allows exactly one of these FKs be non-NULL.
• There is a computed column Id which equals one of the FKs (whichever is currently non-NULL) which is also a PRIMARY KEY. This ensures that:
  • One parent cannot have multiple children.
  • A "grandchild" table can reference the CommonChild.Id directly from its FK. The SuperType.Id is effectively popagated all the way down.
  • We don't have to mess with NULL-able UNIQUE constraints, which are problematic in MS SQL Server (see below).

---

A DBMS-agnostic way of of doing something similar would be...

CREATE TABLE CommonChild (
    Id int PRIMARY KEY,
    SubTypeAId int UNIQUE REFERENCES SubTypeA (SuperId),
    SubTypeBId int UNIQUE REFERENCES SubTypeB (SuperId),
    Attr6 varchar,
    CHECK (
        (SubTypeAId IS NOT NULL AND SubTypeAId = Id AND SubTypeBId IS NULL)
        OR (SubTypeAId IS NULL AND SubTypeBId IS NOT NULL AND SubTypeBId = Id)
    )
)

Unfortunately a UNIQUE column containing more than one NULL is not allowed by MS SQL Server, which is not the case in most DBMSes. However, you can just omit the UNIQUE constraint if you don't want to reference SubTypeAId or SubTypeBId directly.