From: Stefan Scheidegger <Stefan.Scheidegger@gmx.net>
To: pgsql-sql@postgresql.org
Subject: REFERENCES and INHERITS restrictions?
Date: Wed, 27 Feb 2008 10:25:59 +0100
Message-ID: <20080227092559.261470@gmx.net> (raw)
Hi all
I’m confronted with the following problem:
I have a base table with several child tables which also use the parent’s primary key as their own primary key:
CREATE TABLE tbl_parent
(
p_id serial NOT NULL,
p_time timestamp(6) with time zone NOT NULL DEFAULT '1970-01-01 01:00:00+01'::timestamp with time zone,
CONSTRAINT tbl_parent_pkey PRIMARY KEY (p_id)
);
CREATE TABLE tbl_child1
(
c1_something character varying(64) NOT NULL,
CONSTRAINT tbl_child1_pkey PRIMARY KEY (p_id),
) INHERITS (tbl_parent);
CREATE TABLE tbl_child2
(
c2_somethingelse integer NOT NULL,
CONSTRAINT tbl_child2_pkey PRIMARY KEY (p_id),
) INHERITS (tbl_parent);
This works fine so far. Now I got another table that references to the primary key of the parent (I need an n to n relation between another table and an aggregation of all my child tables, which means an n to n relation between the other table and the parent table):
CREATE TABLE tbl_reference
(
ref_id serial NOT NULL,
ref_parent integer NOT NULL,
ref_othertable integer NOT NULL,
CONSTRAINT tbl_reference_pkey PRIMARY KEY (ref_id),
CONSTRAINT tbl_reference_ref_parent_fkey FOREIGN KEY (ref_parent)
REFERENCES tbl_parent (p_id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT tbl_reference_ref_othertable_fkey FOREIGN KEY (ref_othertable)
REFERENCES tbl_othertable (ot_id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE SET NULL
)
When I try to insert into tbl_reference now, I get an sql error:
INSERT INTO tbl_child1(p_time,c1_something) VALUES ('2008-01-01', 'foo');
INSERT 0 1
SELECT * FROM tbl_parent;
p_id | p_time
------+------------------------
1 | 2008-01-01 00:00:00+01
(1 row)
INSERT INTO tbl_reference(ref_parent,ref_othertable) VALUES ('1','1');
ERROR: insert or update on table "tbl_reference" violates foreign key constrain
t "tbl_reference_ref_parent_fkey"
DETAIL: Key (ref_parent)=(1) is not present in table "tbl_parent".
Why is this not possible? It seems that this is about inheritance. When I reference directly to tbl_child1, everything works just fine.
Any idea?
Greets Stefan
--
Ist Ihr Browser Vista-kompatibel? Jetzt die neuesten
Browser-Versionen downloaden: http://www.gmx.net/de/go/browser
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: Stefan.Scheidegger@gmx.net
Subject: Re: REFERENCES and INHERITS restrictions?
In-Reply-To: <20080227092559.261470@gmx.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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