By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,852 Members | 2,220 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,852 IT Pros & Developers. It's quick & easy.

Foreign keys on inherited attributes

P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 22 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 22 '05 #2

P: n/a
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

Nov 22 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.