Thread: Inheritance foreign key unexpected behaviour

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

On 10/20/07, M. van Egmond <mvanegmond@quetalzorg.nl> wrote:
> 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:

From the inheritance docs at
http://www.postgresql.org/docs/8.2/static/ddl-inherit.html

A serious limitation of the inheritance feature is that indexes
(including unique constraints) and foreign key constraints only apply
to single tables, not to their inheritance children. This is true on
both the referencing and referenced sides of a foreign key constraint.

PostgreSQL foreign keys won't enforce restrictions the way you want them to;
you'll have to use a trigger. And at that point, you might as well consider
alternative designs...

--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 722-0567 voice