INHERITS and Foreign keys

Поиск
Список
Период
Сортировка
От Henning.Baldersheim@devoll.no
Тема INHERITS and Foreign keys
Дата
Msg-id 6.0.1.1.2.20031213200950.01d7b640@mail.captech.no
обсуждение исходный текст
Ответы Re: INHERITS and Foreign keys  (Stephan Szabo <sszabo@megazone.bigpanda.com>)
Список 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)



В списке pgsql-sql по дате отправления:

Предыдущее
От: Stephan Szabo
Дата:
Сообщение: Re: Interest IN problem on 7.4
Следующее
От: Stephan Szabo
Дата:
Сообщение: Re: INHERITS and Foreign keys