Thread: Table inherit & foreign key problem

Hi all,

i have problems with tables an foreign keys. i created the following
tables: (i did not copy all the command, only the important (in my point of view)
CREATE TABLE element (   id bigint NOT NULL,   site_id bigint,   type_id bigint NOT NULL,   name character
varying(512),  description text,   active boolean NOT NULL
 
);

CREATE TABLE crmuser (   username character varying(32) NOT NULL,   firstname character varying(64),   lastname
charactervarying(64)
 
)
INHERITS (element);

CREATE TABLE "comment" (   user_id bigint,   created timestamp without time zone,   content text,   element_id bigint
NOTNULL
 
)
INHERITS (element);



CREATE INDEX idx_comment_id ON "comment" USING btree (id);

CREATE INDEX idx_comment_user_id ON "comment" USING btree (user_id);

CREATE INDEX idx_comment_element_id ON "comment" USING btree (element_id);

ALTER TABLE ONLY element   ADD CONSTRAINT element_pkey PRIMARY KEY (id);

ALTER TABLE ONLY crmuser   ADD CONSTRAINT crmuser_pkey PRIMARY KEY (id);


ALTER TABLE ONLY "comment"   ADD CONSTRAINT comment_pkey PRIMARY KEY (id);

ALTER TABLE ONLY "comment"   ADD CONSTRAINT "$3" FOREIGN KEY (user_id) REFERENCES crmuser(id);

ALTER TABLE ONLY "comment"   ADD CONSTRAINT "$4" FOREIGN KEY (element_id) REFERENCES element(id);    




when i try to insert an comment with element_id = id of an user which is
visible when i call "select * from element", i get an foreign key
violation error: element_id=XY is not available in element.

i would like to reference to the element table, because i have some
tables which inherit from element and i would like to add comments to
these elements.

is this impossible or did i do a silly mistake? i did not find a
solution unsing google.


- Moritz




-- 
Wissen ist das einzige Gut, das sich vermehrt, wenn man es teilt.
--
http://www.lagerkochbuch.ch

Moritz Kobel wrote:
> i would like to reference to the element table, because i have some
> tables which inherit from element and i would like to add comments to
> these elements.
> 
> is this impossible or did i do a silly mistake? i did not find a
> solution unsing google.

Foreign-key limitations with inheritance I'm afraid. See the manuals for 
details:

http://www.postgresql.org/docs/8.2/static/ddl-inherit.html
5.8.1 Caveats
...
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. Thus, 
in the terms of the above example:
...

--   Richard Huxton  Archonet Ltd