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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
