Re: Preventing Multiple Inheritance

On Fri, Jun 03, 2005 at 14:09:32 -0500,
Peter Fein <pfein(at)pobox(dot)com> wrote:
> Hi-
>
> Let's say I have a base table B (with a PK id, say) and two derived
> tables D1 & D2 (with different cols). For a given B.id, I'd like to
> allow only a corresponding row in *either* D1 or D2, but not both. Any
> suggestions on how to do this? Should I not be using inheritance at all?
>
> My thought was to add a column inherits_to to B with a value indicating
> whether that row is really a D1 or a D2 and enforce it with appropriate
> CHECK constraints on each of the derived tables.

If it is OK to have no value in either D1 or D2, the simple way to do this
is the following. Have a record type value in B, D1 and D2. In D1 and D2 it
should be constained to have exactly the value that corresponds to that record
type. You need to make the PK of B plus the record type a unique key. And
in D1 and D2 you need to use a foreign key reference that uses the normal
PK plus the record type. This wastes a little space, but is easy to use.
If you need exactly one of D1 or D2 to have a value, then you can have
two fields in B that can either have a copy of the primary key or NULL
and a constraint that exactly one of them is NULL. One of these should
reference D1 and the other D2. You will want to make these last two
deferred constraints.