From: | Michele Bendazzoli <mickymouse(at)mickymouse(dot)it> |
---|---|
To: | pgsql-sql <pgsql-sql(at)postgresql(dot)org> |
Subject: | Bug in Rule+Foreing key constrain? |
Date: | 2003-10-30 12:35:34 |
Message-ID: | 1067517334.2371.26.camel@mickymouse.sintel |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers 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
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
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Dunstan | 2003-10-30 12:36:14 | Re: Fwd: Re: Call for port reports |
Previous Message | Daniel Migowski | 2003-10-30 08:42:47 | CREATE TYPE for case insensitive text and varchar |
From | Date | Subject | |
---|---|---|---|
Next Message | Freshman | 2003-10-30 12:38:14 | Please help me to slove this SQL statements |
Previous Message | Tomasz Myrta | 2003-10-30 12:27:27 | Re: strange postgresql failure |