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? ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings