The following bug has been logged online:

Bug reference: 1636
Logged by: Fredrik Olsson
Email address: peylow (AT) atari (DOT) org
PostgreSQL version: 8.0.2
Operating system: Max OS X 10.3.9
Description: Foreign key referencing inherited table fails.
Details:

Some SQL statements says more then 1000 words .

CREATE TABLE employees (
nr serial PRIMARY KEY,
name varchar(32)
);

CREATE TABLE managers (
loves_meetings boolean DEFAULT TRUE NOT NULL
) INHERITS (employees);

CREATE TABLE events (
summoner integer NOT NULL REFERENCES employees (nr),
at timestamp DEFAULT now() NOT NULL,
reason text NOT NULL,
PRIMARY KEY (summoner, at)
);

INSERT INTO employees (name) VALUES ('Bob');
INSERT INTO managers (name) VALUES ('Alice');

-- This makes an ugly assumption of nr starting at 1 and Alice having nr=2.

INSERT INTO events (summoner, reason) VALUES (2, 'Brag about
inheritance.');

The last statement will fail with this error message:
ERROR: insert or update on table "events" violates foreign key constraint
"events_summoner_fkey"
DETAIL: Key (summoner)=(2) is not present in table "employees".

The foreign key from events (summoner) referencing employees (nr) can be
removed and replaced by triggers with ease. But I still think it is a bug,
and that foreign key references to tables that are inherited from should be
legal, even if referenced rows orginates from child tables.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

On Thu, Apr 28, 2005 at 04:31:45PM +0100, Fredrik Olsson wrote:
This is a documented limitation of inheritance. Fixing inheritance
shortcomings is on the developers' TODO list; I'm sure a patch would
be welcome :-)

http://www.postgresql.org/docs/8.0/i...l-inherit.html
http://www.postgresql.org/docs/faqs.TODO.html

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org