Date: Fri, 9 Mar 2007 12:43:03 +0100
From: Moritz Kobel <mailinglists@weisshorn.kobelnet.ch>
To: pgsql-sql@postgresql.org
Subject: Table inherit & foreign key problem
Message-ID: <20070309114303.GC12129@alvier.kobelnet.intra>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: mutt-ng/devel-r804 (Debian)

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 character varying(64)
)
INHERITS (element);

CREATE TABLE "comment" (
    user_id bigint,
    created timestamp without time zone,
    content text,
    element_id bigint NOT NULL
)
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