Skip site navigation (1) Skip section navigation (2)

fkey+inheritance issue

From: Gergely CZUCZY <phoemix(at)harmless(dot)hu>
To: pgsql-bugs(at)postgresql(dot)org
Subject: fkey+inheritance issue
Date: 2006-12-13 12:32:37
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-bugs

i had written a bug report on the web-based for, but
it hadn't accepted my email address, so i'm trying to
report this here.

PostgreSQL version:
PostgreSQL 8.2.0 on i386-portbld-freebsd6.1, compiled by GCC cc (GCC) 3.4.4 [FreeBSD] 20050518

OS: FreeBSD 6.1-RELEASE-p10

Short descr:
fkey cannot reference to an inherited table's child's tuple through the ancestor

hen given an inherited table structure and one other table's FKEY uses the supertable's inherited PKEY as a reference, it gets an error when it comes to child table's tuples.

whereas in sql-createtable.html it's said that then children's data can be accessed through the parent table(s).

the following code demonstrates the issue well:

CREATE TABLE "ancestor" (
        "id"            integer         NOT NULL,
        "name"          varchar(32)     NOT NULL,
        PRIMARY KEY     ("id")

CREATE TABLE "child" (
        "attr1"         integer         NOT NULL
) INHERITS ("ancestor");

CREATE TABLE "reference" (
        "id"            integer         NOT NULL,
        "ancid"         integer         NOT NULL,
        PRIMARY KEY     ("id"),
        FOREIGN KEY     ("ancid")       REFERENCES      "ancestor"("id")

INSERT INTO "ancestor" (id,name) VALUES (1,'ancfoo');
INSERT INTO "child" (id,name,attr1) VALUES (2,'childfoo', 2);
INSERT INTO "reference" (id,ancid) VALUES (1,1);
-- till here it's OK
INSERT INTO "reference" (id,ancid) VALUES (2,2);
-- here:
-- ERROR:  insert or update on table "reference" violates foreign key constraint "reference_ancid_fkey"
-- DETAIL:  Key (ancid)=(2) is not present in table "ancestor".


Gergely Czuczy
mailto: gergely(dot)czuczy(at)harmless(dot)hu

Weenies test. Geniuses solve problems that arise.

pgsql-bugs by date

Next:From: Jean-GĂ©rard PailloncyDate: 2006-12-13 15:03:43
Subject: 8.1.2 -32768::smallint
Previous:From: Gergely CZUCZYDate: 2006-12-13 12:30:35
Subject: the bug report form doesn't accepts the email address

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group