From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Michele Bendazzoli <mickymouse@mickymouse.it>
Cc: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: Bug in Rule+Foreing key constrain?
Date: Thu, 30 Oct 2003 07:04:39 -0800 (PST)
Message-ID: <20031030070237.W39861@megazone.bigpanda.com> (raw)
In-Reply-To: <1067517334.2371.26.camel@mickymouse.sintel>
References: <1067517334.2371.26.camel@mickymouse.sintel>
On Thu, 30 Oct 2003, Michele Bendazzoli wrote:
> I have found a strange behaviour that I don't know if is a bug or not.
>
> I have three tables:
> * abilitazione with a primary key of (comuneid, cassonettoid, chiaveid)
> * cassonetto with a primary key of (comuneid, cassonettoid)
> * chiave with a primary key of (comuneid, chiaveid)
>
> and two foreign key from abilitazioni to cassonetto and chiave tables
> respectivly. Here is the picture (I hope it will be clear)
>
> cassonetto abilitazioni chiave
> |comuneid |--> |comuneid |<---|comuneid|
> PK -> |cassonettoid|--> |cassonettoid| | |
> |chiaveid |<---|chiaveid|
> |abilitata |
>
> Whenever I try to insert a row in abilitazione that hasn't a match row
> in cassonetto or abilitazione, an exception is raised (referential
> integrity violation : key referenced from abilitazione not found in
> cassonetto).
>
> So far, so good.
>
> Now if I Add a rule to abilitazione in order to avoid a duplication of a
> row when a user try to insert one with an existing primary key:
>
> CREATE OR REPLACE RULE abilita_ins_rl AS ON INSERT TO abilitazione
> WHERE (EXISTS (
> SELECT 1
> FROM abilitazione
> WHERE (((abilitazione.comuneid = new.comuneid )
> AND (abilitazione.cassonettoid = new.cassonettoid ))
> AND (abilitazione.chiaveid = new.chiaveid ))))
> DO INSTEAD UPDATE abilitazione SET abilitata = new.abilitata
> WHERE (((abilitazione.comuneid = new.comuneid )
> AND (abilitazione.cassonettoid = new.cassonettoid ))
> AND (abilitazione.chiaveid = new.chiaveid ));
>
> the constraints related to the foreign kesy disappears (i.e. I'm able to
> insert a row in abilitazioni that hasn't a key referenced in cassonetto
> or abilitazioni).
>
> Is this a bug or a feauture? If is a feature, it is possible to
Hmm, I'd say a bug, but can you send a standalone example that replicates
it?
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: sszabo@megazone.bigpanda.com, mickymouse@mickymouse.it
Subject: Re: Bug in Rule+Foreing key constrain?
In-Reply-To: <20031030070237.W39861@megazone.bigpanda.com>
* 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