Peter Fein <pfein@pobox.com> writes:
> 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 can only be one or the other, I think you're misdesigning the
thing. Use *one* table with all the columns needed to describe either a
D1 or D2. Leave the unneeded columns NULL in any given row. (If
needed, you can have a check constraint that specifies that certain
columns are not null when it's a D1, etc.) Null entries are cheap.
regards, tom lane