Hello 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
Details:
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".
Bye,
Gergely Czuczy
mailto: [EMAIL PROTECTED]
--
Weenies test. Geniuses solve problems that arise.
pgp5jMHmJXV5T.pgp
Description: PGP signature
