pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedINHERITS and Foreign keys
5+ messages / 4 participants
[nested] [flat]
* INHERITS and Foreign keys
@ 2003-12-13 19:43 Henning.Baldersheim@devoll.no <Henning.Baldersheim@devoll.no>
0 siblings, 1 reply; 5+ messages in thread
From: Henning.Baldersheim@devoll.no @ 2003-12-13 19:43 UTC (permalink / raw)
To: pgsql-sql
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)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: INHERITS and Foreign keys
@ 2003-12-13 19:55 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Henning.Baldersheim@devoll.no <Henning.Baldersheim@devoll.no>
0 siblings, 0 replies; 5+ messages in thread
From: Stephan Szabo @ 2003-12-13 19:55 UTC (permalink / raw)
To: Henning.Baldersheim@devoll.no <Henning.Baldersheim@devoll.no>; +Cc: pgsql-sql
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.)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: INHERITS and Foreign keys
@ 2003-12-13 21:07 Henning.Baldersheim@devoll.no <Henning.Baldersheim@devoll.no>
0 siblings, 1 reply; 5+ messages in thread
From: Henning.Baldersheim@devoll.no @ 2003-12-13 21:07 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: INHERITS and Foreign keys
@ 2003-12-19 23:51 Pedro <pladen@elv.enic.fr>
parent: Henning.Baldersheim@devoll.no <Henning.Baldersheim@devoll.no>
0 siblings, 1 reply; 5+ messages in thread
From: Pedro @ 2003-12-19 23:51 UTC (permalink / raw)
To: pgsql-sql
>> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: INHERITS and Foreign keys
@ 2003-12-23 13:13 Chris Travers <chris@travelamericas.com>
parent: Pedro <pladen@elv.enic.fr>
0 siblings, 0 replies; 5+ messages in thread
From: Chris Travers @ 2003-12-23 13:13 UTC (permalink / raw)
To: Pedro <pladen@elv.enic.fr>; pgsql-sql
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
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2003-12-23 13:13 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-12-13 19:43 INHERITS and Foreign keys Henning.Baldersheim@devoll.no <Henning.Baldersheim@devoll.no>
2003-12-13 19:55 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-12-13 21:07 Re: INHERITS and Foreign keys Henning.Baldersheim@devoll.no <Henning.Baldersheim@devoll.no>
2003-12-19 23:51 ` Pedro <pladen@elv.enic.fr>
2003-12-23 13:13 ` Chris Travers <chris@travelamericas.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox