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@3io.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.