From: Henning.Baldersheim@devoll.no <Henning.Baldersheim@devoll.no>
To: pgsql-sql@postgresql.org
Subject: INHERITS and Foreign keys
Date: Sat, 13 Dec 2003 20:43:44 +0100
Message-ID: <6.0.1.1.2.20031213200950.01d7b640@mail.captech.no> (raw)
In-Reply-To: <20031213190142.C41B4B9C05E@svr5.postgresql.org>
References: <20031213190142.C41B4B9C05E@svr5.postgresql.org>
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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: Henning.Baldersheim@devoll.no
Subject: Re: INHERITS and Foreign keys
In-Reply-To: <6.0.1.1.2.20031213200950.01d7b640@mail.captech.no>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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