Subject: Bug in Rule+Foreing key constrain?
From: Michele Bendazzoli <mickymouse@mickymouse.it>
To: pgsql-sql <pgsql-sql@postgresql.org>
Content-Type: text/plain
Organization: Micky Mouse
Message-Id: <1067517334.2371.26.camel@mickymouse.sintel>
Mime-Version: 1.0
Date: Thu, 30 Oct 2003 13:35:34 +0100
Content-Transfer-Encoding: 7bit

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
abilitate again the check of the contraints?

Thank you in advance for any advice.

ciao, Michele