Re: Foreign keys on inherited attributes

On Mon, 2004-01-26 at 19:23, Shawn Harrison wrote:

> I'm using pg 7.3.5 and playing with table inheritance, and I've run into the
> fact that foreign keys cannot be defined on inherited attributes. (As much
> is stated in the documentation, but it didn't sink in until I ran into the
> fact.)

I have a similar problem and have two inelegant workarounds.

1) Use triggers which does the key check. I don't know of any simple way
to cascade deletes or updates.

2) Use inheritance to define the FK-containing tables as well, one for
each PK-containing table. The supertable of these is provides the
abstraction you sought.

Example:

I wanted a hierarchy of models of different types (each with
table-specific data) and heterogeneous sets of models, roughly like
this:
(if the word model is distracting, think instead of jobs and sets of
related job families, or some such analogy)

model modelA modelB modelC (model{A,B,C} ISA model)
mid(PK) mid(PK) mid(PK) mid(PK)
colA1 colB1 colC1

setmodel
mid(FK)
sid(FK)

set
sid(PK)
name

As you noted, making modelsetmodel.mid a FK of model.mid doesn't work
because the PK index is NOT inherited.
Instead, I have this:

model modelA modelB modelC (model{A,B,C} ISA model)
mid(PK) mid(PK) mid(PK) mid(PK)
colA1 colB1 colC1

setmodel setmodelA setmodelB setmodelC (setmodel{A,B,C} ISA setmodel)
mid(FK) mid(FK) mid(FK) mid(FK)
sid(FK) sid(FK) sid(FK) sid(FK)

set
sid(PK)
name

Of course, setmodelA.mid is now a FK of modelA.mid, and so on for B and
C. I can still select from setmodel to get the heterogeneous sets I
originally sought.

I hope that helps,
Reece

--
Reece Hart, http://www.in-machina.com/~reece/, GPG:0x25EC91A0