Thread: INHERITS and Foreign keys

Hi

I have some problem with INHERITS and foreign keys. I do not know if I have 
not got the clue or not. Anyway I have tried to simplify the problem so 
that you can guide me on the right track.

I have two slightly different object A and B where 95% is common both of 
data and operations. Among those are the primary key. I then put the common 
stuff into P and let A and B inherit from P. It seems to work fine and 
behaves like I assumed, except for foreign keys. I have a different object 
L that links to P through P's PRIMARY KEY. But when I try do make an insert 
it fails with foreign key constraint failure.

Below is a compressed code example illustrating the problem. If anyone can 
tell me if I am trying the impossible, it is a bug, a todo, a never do, or 
a patch, I would be very happy. I did search through the mailing lists to 
see if I could find the answer. I found some articles about inheritance and 
foreign keys, but I could not see the relevance to my problem.

My assumption is that everything you add to A and B will be seen in P. The 
results of the selects indicates that I am correct. However the behavior of 
the FOREIGN KEY indicates that that is not true. The references can see ONLY P.

I am using version postgresql 7.4.

Code:
CREATE TABLE p(id SERIAL PRIMARY KEY);
CREATE TABLE a(a char(2)) INHERITS(p);
CREATE TABLE b(b char(2)) INHERITS(p);

INSERT INTO p(id) VALUES(default);
INSERT INTO p(id) VALUES(default);
INSERT INTO a(a) VALUES('a1');
INSERT INTO a(a) VALUES('a2');
INSERT INTO b(b) VALUES('b1');
INSERT INTO b(b) VALUES('b2');

CREATE TABLE l(id SERIAL PRIMARY KEY, pRef bigint REFERENCES p(id));

INSERT INTO l(pRef) VALUES(1);
INSERT INTO l(pRef) VALUES(2);
INSERT INTO l(pRef) VALUES(3); --- This one fails ! You see it is there on 
the select * from p.
psql:testInheritRef.sql:25: ERROR:  insert or update on table "l" violates 
foreign key constraint "$1"
DETAIL:  Key (pref)=(3) is not present in table "p".

Here is result from SELECT.
SELECT * FROM p; id
----  1  2  3  4  5  6
(6 rows)

SELECT * FROM ONLY p; id
----  1  2
(2 rows)

SELECT * FROM a; id | a
----+----  3 | a1  4 | a2
(2 rows)

SELECT * FROM b; id | b
----+----  5 | b1  6 | b2
(2 rows)

On Sat, 13 Dec 2003, Henning.Baldersheim@devoll.no wrote:

> Hi
>
> I have some problem with INHERITS and foreign keys. I do not know if I have
> not got the clue or not. Anyway I have tried to simplify the problem so
> that you can guide me on the right track.

Foreign keys, unique and primary key constraints do not meaningfully
inherit currently.  At some point in the future, that's likely to change,
but for now you're pretty much stuck with workarounds (for example, using
a separate table to store the ids and triggers/rules on each of the tables
in the hierarchy in order to keep the id table in date.)

At 20:55 12/13/2003, you wrote:

>On Sat, 13 Dec 2003, Henning.Baldersheim@devoll.no wrote:
>
> > Hi
> >
> > I have some problem with INHERITS and foreign keys. I do not know if I have
> > not got the clue or not. Anyway I have tried to simplify the problem so
> > that you can guide me on the right track.
>
>Foreign keys, unique and primary key constraints do not meaningfully
>inherit currently.  At some point in the future, that's likely to change,
>but for now you're pretty much stuck with workarounds (for example, using
>a separate table to store the ids and triggers/rules on each of the tables
>in the hierarchy in order to keep the id table in date.)

Is there a time frame for this ? a patch, a month, a year, 7.5 or a beta 
version ?

Thank you for the quick response.

Henning

>> Foreign keys, unique and primary key constraints do not meaningfully
>> inherit currently.  At some point in the future, that's likely to change,
>> but for now you're pretty much stuck with workarounds (for example, using
>> a separate table to store the ids and triggers/rules on each of the 
>> tables
>> in the hierarchy in order to keep the id table in date.)

hi

same problem here on 7.4
can we vote for this bug somewhere ?!

thanks for your time
Pedro

Hi Pedro;

I understand that at the moment it is more of a design limitation than a
bug.  I think we should vote the desired behavior as a feature request,
however.

Best Wishes,
Chris Travers
----- Original Message -----
From: "Pedro" <pladen@elv.enic.fr>


> >> Foreign keys, unique and primary key constraints do not meaningfully
> >> inherit currently.  At some point in the future, that's likely to
change,
> >> but for now you're pretty much stuck with workarounds (for example,
using
> >> a separate table to store the ids and triggers/rules on each of the
> >> tables
> >> in the hierarchy in order to keep the id table in date.)
>
> hi
>
> same problem here on 7.4
> can we vote for this bug somewhere ?!
>
> thanks for your time
> Pedro
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>