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
