Bug in Rule+Foreing key constrain?

From: Michele Bendazzoli <mickymouse(at)mickymouse(dot)it>
To: pgsql-sql <pgsql-sql(at)postgresql(dot)org>
Subject: Bug in Rule+Foreing key constrain?
Date: 2003-10-30 12:35:34
Message-ID: 1067517334.2371.26.camel@mickymouse.sintel
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers 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
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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Andrew Dunstan 2003-10-30 12:36:14 Re: Fwd: Re: Call for port reports
Previous Message Daniel Migowski 2003-10-30 08:42:47 CREATE TYPE for case insensitive text and varchar

Browse pgsql-sql by date

  From Date Subject
Next Message Freshman 2003-10-30 12:38:14 Please help me to slove this SQL statements
Previous Message Tomasz Myrta 2003-10-30 12:27:27 Re: strange postgresql failure