Inheritance foreign key unexpected behaviour

["M. van Egmond" <mvanegmond@xxxxxxxxxxxxx>]

Hi all,

Im trying to use table inheritance in my database. I need it because i want to be able to link any object in the database to another. So i created a table my_object which has a serial, nothing more. All the other tables in the system are inherited from this my_object table. Im having difficulties adding foreign keys to the tables. This is my test setup:

PostgreSQL 8.2.5 on Windows

-- BEGIN OF SQL

CREATE TABLE my_object
(
  id serial NOT NULL,
  CONSTRAINT "myobject_PK" PRIMARY KEY (id)
) 
WITHOUT OIDS;

CREATE TABLE my_child
(
  title text,
  CONSTRAINT "child_PK" PRIMARY KEY (id)
) INHERITS (my_object) 
WITHOUT OIDS;

CREATE TABLE my_link
(
  foreign_object_id integer,
  CONSTRAINT "link_PK" PRIMARY KEY (id),
  CONSTRAINT "link_FK_object" FOREIGN KEY (foreign_object_id)
      REFERENCES my_object (id) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT
) INHERITS (my_object) 
WITHOUT OIDS;

INSERT INTO my_child(title) values('test object');

-- Now when i try to add a row to my_link referencing to the newly created object in the my_child table and thus also available in the my_object table.

INSERT INTO my_link(foreign_object_id) values(1);

-- I get ERROR: insert or update on table "my_link" violates foreign key constraint "link_FK_object"
-- SQL status:23503
-- Detail:Key (foreign_object_id)=(1) is not present in table "my_object".

-- But if we do a simple select from the my_object table:

SELECT * FROM my_object WHERE id=1;

-- We do get the row.

-- END_OF_SQL


So what's wrong here? Is this improper use of the inheritance features or a bug? Please help!

Thanks!

Matthieu van Egmond