Thread: Foreign keys on inherited attributes

Foreign keys on inherited attributes

From
"Shawn Harrison"
Date:
Greetings,

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.)

The documents say this will probably be fixed in a future release. My
question is, are there any definite plans in that direction at this point?

Take care,
Shawn Harrison


Re: Foreign keys on inherited attributes

From
Stephan Szabo
Date:
On Mon, 26 Jan 2004, 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.)
>
> The documents say this will probably be fixed in a future release. My
> question is, are there any definite plans in that direction at this point?

AFAIK, there's nobody actively looking at dealing with the various
constraint/inheritance issues at this point.


Re: Foreign keys on inherited attributes

From
Reece Hart
Date:
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