Re: Bug in Rule+Foreing key constrain?

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?