Date: Mon, 22 Mar 2004 13:40:11 +0200 (EET)
From: Achilleus Mantzios <achill@matrix.gatewaynet.com>
To: Erik Thiele <erik@thiele-hydraulik.de>
Cc: pgsql-sql@postgresql.org
Subject: Re: special integrity constraints
In-Reply-To: <20040322112659.4553df76.erik@thiele-hydraulik.de>
Message-ID: <Pine.LNX.4.44.0403221322020.31407-100000@matrix.gatewaynet.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

O kyrios Erik Thiele egrapse stis Mar 22, 2004 :

> On Mon, 22 Mar 2004 12:13:29 +0200 (EET)
> Achilleus Mantzios <achill@matrix.gatewaynet.com> wrote:
> 
> > O kyrios Erik Thiele egrapse stis Mar 22, 2004 :
> > 
> > Did you check out the DEFERRABLE option on the constraint?
> > 
> 
> DEFERRABLE
> NOT DEFERRABLE
> 
> This controls whether the constraint can be deferred. A constraint that
> is not deferrable will be checked immediately after every command.
> Checking of constraints that are deferrable may be postponed until the
> end of the transaction (using the SET CONSTRAINTS command). NOT
> DEFERRABLE is the default. 
> 
> !!!! Only foreign key constraints currently accept
> this clause. All other constraint types are not deferrable. !!!!
> 
Ooops . Then i guess you have to
convert your problem to use FK constraints.

Add 2 additional 1 row tables "abnum" and "even" and write 2 triggers
(on a,b) that keep up to date the value of the single row of abnum.
(thatis num(a)+num(b) % 2).
Then INSERT INTO even(num) VALUES(0);

Then make 1<-->1 relationship between abnum,even using
DEFERRABLE FK constraints.

At the end of each xaction the num(a)+num(b) % 2 must equal to 0
(i.e. an even number).

Whats your results?

> my constraint is not a foreign key constraint....
> 
> cya!
> erik
> 
> > > hi,
> > > 
> > > i have two tables
> > > 
> > > create table a (x integer);
> > > create table b (y real);
> > > 
> > > i have the special constraint that the sum of the number of rows in
> > > table a plus the number of rows in table b must be even.
> > > 
> > > so there is a posibility of adding one element to a and one element
> > > to b, and again the constraint is met.
> > > 
> > > but this of course does not work, since between adding the element
> > > to a and adding the element to b, the constraint is not met.
> > > 
> > > so i cannot use a trigger.
> > > 
> > > what i need is the execution of a check procedure at commit time. is
> > > that somehow possible?
> > > 
> > > my database has much more tables than just a and b, and most time
> > > the other tables are modified and not a or b, so it would be nice to
> > > execute the constraint checking procedure only if a or b was
> > > modified.
> > > 
> > > yes :) this question i asked in my former mail too, but I think it
> > > looked like there was a workaround and my problem was wrong. so i
> > > created this new kind of problem here to make things clearer.
> > > 
> > > 
> > > cu
> > > Erik
> > > 
> > > 
> > > 
> > 
> > -- 
> > -Achilleus
> > 
> 
> 
> 

-- 
-Achilleus