Bug in Rule+Foreing key constrain?
От | Michele Bendazzoli |
---|---|
Тема | Bug in Rule+Foreing key constrain? |
Дата | |
Msg-id | 1067517334.2371.26.camel@mickymouse.sintel обсуждение исходный текст |
Ответы |
Re: Bug in Rule+Foreing key constrain?
Re: Bug in Rule+Foreing key constrain? |
Список | 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 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
В списке pgsql-sql по дате отправления: