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