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

referential integrity through inherited tables

From: "Josh Goldberg" <josh(at)4dmatrix(dot)com>
To: <pgsql-bugs(at)postgresql(dot)org>
Subject: referential integrity through inherited tables
Date: 2002-06-17 22:39:39
Message-ID: 045f01c2164f$dd9915e0$3e02a8c0@4dmatrix.com (view raw or flat)
Thread:
Lists: pgsql-bugs
This is probably more like an "undesired feature" than a software bug, but
it was behaviour that I did not expect.

thanks!

Your name               :       Josh Goldberg
Your email address      :       josh(at)3io(dot)com


System Configuration
---------------------
  Architecture (example: Intel Pentium)         :
        intel P3

  Operating System (example: Linux 2.0.26 ELF)  :
        Linux 2.2

  PostgreSQL version (example: PostgreSQL-7.2.1):   PostgreSQL-7.2.1

  Compiler used (example:  gcc 2.95.2)          :


Please enter a FULL description of your problem:
------------------------------------------------
Foreign key checks fail when referenced row is in an inherited table.

In the reproduction procedure below, if you select * from foo it returns
the record from table bar, however a key referencing foo will fail when
you want it to reference the record that was inserted into bar even though
it appears as a part of the foo table via inheritance.




Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------
CREATE TABLE foo(id1 int4,id2 int4,id3 int4);
CREATE TABLE bar(id4 int4) inherits(foo);
CREATE TABLE baz(id1 int4,troz int4);
ALTER TABLE baz ADD CONSTRAINT bazfk FOREIGN KEY (id1) REFERENCES foo(id1)
MATCH FULL;

INSERT INTO bar(1,2,3,4);
INSERT INTO baz(1,5);
ERROR: bazfk referential integrity violation - key referenced from baz not
found in foo



If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------
Add a way to denote that a constraint should check children tables
in addition to the one named in the constraint.  perhaps something like

ALTER TABLE baz ADD CONSTRAINT bazfk FOREIDNG KEY (id1) REFERENCES foo*(id1)
MATCH FULL;

or have it check children tables by default and do something like

ALTER TABLE baz ADD CONSTRAINT bazfk FOREIDNG KEY (id1) REFERENCES ONLY
foo(id1) MATCH FULL;

to only check foo.  similar syntax to select statements.



pgsql-bugs by date

Next:From: David M. KaplanDate: 2002-06-18 03:47:27
Subject: pg_hba.conf confusion
Previous:From: Aaron MulderDate: 2002-06-17 13:52:21
Subject: JDBC Metadata Problems

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