pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Table inherit & foreign key problem
2+ messages / 2 participants
[nested] [flat]

* Table inherit & foreign key problem
@ 2007-03-09 11:43  Moritz Kobel <mailinglists@weisshorn.kobelnet.ch>
  0 siblings, 1 reply; 2+ messages in thread

From: Moritz Kobel @ 2007-03-09 11:43 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Table inherit & foreign key problem
@ 2007-03-09 12:35  Richard Huxton <dev@archonet.com>
  parent: Moritz Kobel <mailinglists@weisshorn.kobelnet.ch>
  0 siblings, 0 replies; 2+ messages in thread

From: Richard Huxton @ 2007-03-09 12:35 UTC (permalink / raw)
  To: Moritz Kobel <mailinglists@weisshorn.kobelnet.ch>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2007-03-09 12:35 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-03-09 11:43 Table inherit & foreign key problem Moritz Kobel <mailinglists@weisshorn.kobelnet.ch>
2007-03-09 12:35 ` Richard Huxton <dev@archonet.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox