Bug in Rule+Foreing key constrain? - Mailing list pgsql-sql

From Michele Bendazzoli
Subject Bug in Rule+Foreing key constrain?
Date
Msg-id 1067517334.2371.26.camel@mickymouse.sintel
Whole thread Raw
Responses Re: Bug in Rule+Foreing key constrain?
Re: Bug in Rule+Foreing key constrain?
List pgsql-sql
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
FROMabilitazione             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
abilitate again the check of the contraints?

Thank you in advance for any advice.

ciao, Michele



pgsql-sql by date:

Previous
From: "Kumar"
Date:
Subject: Re: Using UNION inside a cursor
Next
From: Tomasz Myrta
Date:
Subject: strange postgresql failure