On Fri, Jun 03, 2005 at 14:09:32 -0500, Peter Fein <pfein@xxxxxxxxx> 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. ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@xxxxxxxxxxxxxx
