Problem with referential integrity and inherited tables in 7.1.1

From: Stefan Loidl <Stefan(dot)Loidl(at)lrz-muenchen(dot)de>
To: pgsql-general(at)postgresql(dot)org
Subject: Problem with referential integrity and inherited tables in 7.1.1
Date: 2001-05-14 07:57:15
Message-ID: 3AFF8FDB.1EF96FAB@lrz-muenchen.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


Hi,

the following (simplified) example works with 7.0.3,
but fails with 7.1.1:

CREATE TABLE ViewElement (
id serial
);

CREATE TABLE ViewNode (
nodeType char
) INHERITS (ViewElement);

CREATE TABLE ViewLink (
linkType char
) INHERITS (ViewElement);

CREATE TABLE ConnectedViewNodes (
linkId int4 NOT NULL CONSTRAINT A REFERENCES ViewLink (id),
nodeId int4 NOT NULL CONSTRAINT B REFERENCES ViewNode (id)
);

After the last create statement I get the following error:
ERROR: UNIQUE constraint matching given keys for referenced table
"viewlink" not found

If I reference the ViewElement table instead of the inherited tables,
I can create the ConnectedViewNodes table, but I can't insert into
ConnectedViewNodes:

CREATE TABLE ConnectedViewNodes (
linkId int4 NOT NULL CONSTRAINT A REFERENCES ViewElement (id),
nodeId int4 NOT NULL CONSTRAINT B REFERENCES ViewElement (id)
);

INSERT INTO ViewNode (nodeType) VALUES ('a');
INSERT INTO ViewLink (linkType) VALUES ('b');

INSERT INTO ConnectedViewNodes VALUES (2, 1);

Here I get the following error after the last insert:
ERROR: b referential integrity violation - key referenced from
connectedviewnodes not found in viewelement

Is this a bug in 7.1.1 or is there an other way to do this?
(Both ways work with 7.0.3)

Thanks,

Stefan

--
Stefan Loidl Phone: +49 89 289-28882
Leibniz Supercomputing Center Fax: +49 89 2809460
Barer Str. 21 mailto:loidl(at)lrz(dot)de
80333 Munich, Germany http://www.lrz.de

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alexander Lohse 2001-05-14 09:13:15 Speeding up Query
Previous Message J.H.M. Dassen Ray 2001-05-14 07:39:25 Re: In a fix: lost superuser