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
|
Список | 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 по дате отправления: