Subject: Re: Bug in Rule+Foreing key constrain?
From: Michele Bendazzoli <mickymouse@mickymouse.it>
To: Jan Wieck <JanWieck@Yahoo.com>
Cc: pgsql-sql <pgsql-sql@postgresql.org>
In-Reply-To: <3FA14A86.8080802@Yahoo.com>
References: <1067517334.2371.26.camel@mickymouse.sintel>
	<3FA14A86.8080802@Yahoo.com>
Content-Type: text/plain; charset=ISO-8859-1
Organization: Micky Mouse
Message-Id: <1067540494.2420.45.camel@mickymouse.sintel>
Mime-Version: 1.0
Date: Thu, 30 Oct 2003 20:01:34 +0100
Content-Transfer-Encoding: 8bit

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