pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedBug in Rule+Foreing key constrain?
4+ messages / 3 participants
[nested] [flat]
* Bug in Rule+Foreing key constrain?
@ 2003-10-30 12:35 Michele Bendazzoli <mickymouse@mickymouse.it>
2003-10-30 15:04 ` Re: Bug in Rule+Foreing key constrain? Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-10-30 17:29 ` Re: Bug in Rule+Foreing key constrain? Jan Wieck <JanWieck@Yahoo.com>
0 siblings, 2 replies; 4+ messages in thread
From: Michele Bendazzoli @ 2003-10-30 12:35 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Bug in Rule+Foreing key constrain?
2003-10-30 12:35 Bug in Rule+Foreing key constrain? Michele Bendazzoli <mickymouse@mickymouse.it>
@ 2003-10-30 15:04 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
1 sibling, 0 replies; 4+ messages in thread
From: Stephan Szabo @ 2003-10-30 15:04 UTC (permalink / raw)
To: Michele Bendazzoli <mickymouse@mickymouse.it>; +Cc: pgsql-sql
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?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Bug in Rule+Foreing key constrain?
2003-10-30 12:35 Bug in Rule+Foreing key constrain? Michele Bendazzoli <mickymouse@mickymouse.it>
@ 2003-10-30 17:29 ` Jan Wieck <JanWieck@Yahoo.com>
2003-10-30 19:01 ` Re: Bug in Rule+Foreing key constrain? Michele Bendazzoli <mickymouse@mickymouse.it>
1 sibling, 1 reply; 4+ messages in thread
From: Jan Wieck @ 2003-10-30 17:29 UTC (permalink / raw)
To: Michele Bendazzoli <mickymouse@mickymouse.it>; +Cc: pgsql-sql
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 |
Not entirely. On which table(s) are the REFERENCES constraints and are
they separate per column constraints or are they multi-column constraints?
It's usually best to cut'n'paste the CREATE TABLE or ALTER TABLE
statements that are used to create the constraints. That way we know
exactly what you're talking about.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Bug in Rule+Foreing key constrain?
2003-10-30 12:35 Bug in Rule+Foreing key constrain? Michele Bendazzoli <mickymouse@mickymouse.it>
2003-10-30 17:29 ` Re: Bug in Rule+Foreing key constrain? Jan Wieck <JanWieck@Yahoo.com>
@ 2003-10-30 19:01 ` Michele Bendazzoli <mickymouse@mickymouse.it>
0 siblings, 0 replies; 4+ messages in thread
From: Michele Bendazzoli @ 2003-10-30 19:01 UTC (permalink / raw)
To: Jan Wieck <JanWieck@Yahoo.com>; +Cc: pgsql-sql
On Thu, 2003-10-30 at 18:29, Jan Wieck wrote:
> Not entirely. On which table(s) are the REFERENCES constraints and are
> they separate per column constraints or are they multi-column constraints?
here are the constraints of the abilitazione table
ALTER TABLE public.abilitazione
ADD CONSTRAINT abilitazione_pkey PRIMARY KEY(comuneid, cassonettoid,
chiaveid);
ALTER TABLE public.abilitazione
ADD CONSTRAINT abilitazione_cassonettoid_fkey FOREIGN KEY (comuneid,
cassonettoid) REFERENCES public.cassonetto (comuneid, cassonettoid) ON
UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE public.abilitazione
ADD CONSTRAINT abilitazione_chiaveid_fkey FOREIGN KEY (comuneid,
chiaveid) REFERENCES public.chiave (comuneid, chiaveid) ON UPDATE
RESTRICT ON DELETE RESTRICT;
here those of cassonetto and chiave:
ALTER TABLE public.cassonetto
ADD CONSTRAINT cassonetto_pkey PRIMARY KEY(comuneid, cassonettoid);
ALTER TABLE public.chiave
ADD CONSTRAINT chiave_pkey PRIMARY KEY(comuneid, chiaveid);
I get the SQL from pgAdmin3 (great piece of sofware!;-)
> It's usually best to cut'n'paste the CREATE TABLE or ALTER TABLE
> statements that are used to create the constraints. That way we know
> exactly what you're talking about.
Excuse me for the missing SQL, but i had tried to keep the message as
simple as possible.
The unique difference form when the exception was raised and now (that
it isn't) is the rule added:
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 ));
I hope now is more clear.
The version is that come with debian unstable (7.3.4 if I remember
correctly)
Thank you for the immediate responses
ciao, Michele
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-10-30 19:01 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-10-30 12:35 Bug in Rule+Foreing key constrain? Michele Bendazzoli <mickymouse@mickymouse.it>
2003-10-30 15:04 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-10-30 17:29 ` Jan Wieck <JanWieck@Yahoo.com>
2003-10-30 19:01 ` Michele Bendazzoli <mickymouse@mickymouse.it>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox