public inbox for [email protected]  

help / color / mirror / Atom feed

From: Erik Thiele <[email protected]>
To: Bruno Wolff III <[email protected]>
Cc: [email protected]
Subject: Re: special integrity constraints
Date: Mon, 22 Mar 2004 14:10:42 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>

On Mon, 22 Mar 2004 05:26:04 -0600
Bruno Wolff III <[email protected]> wrote:

> On Mon, Mar 22, 2004 at 10:20:31 +0100,
>   Erik Thiele <[email protected]> wrote:
> > 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.
> 
> Does the above mean that the number of rows in a is equal to the
> number of rows in b or that their sum is divisible by 2?

it means (((count_rows(a)+count_rows(b)) modulo 2) == 0)

> > 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?
> 
> Do the real tables have candidate keys? If so you can use foreign key
> references to pair a row in a to a row b (though I am not sure this
> is what your real constraint is).

no. it's not what i want. the example presented here is just for making
it clear why i want to launch a postgresql function on commit time. i am
looking for this command:

IF TABLE A OR B MODIFIED DO CALL my_checking_function() BEFORE COMMIT;

and of course this must be enforced on DB layer. user apps may not work
around it.

as a dirty hack the following would also work for a first:

ON COMMIT CALL my_checking_function();

altough it does lots of unneccessary work if the tables were not
accessed.

the my_checking_function() throws an error and thus aborts the
transaction if the consistency is not ok. (num(a)+num(b) odd)

my_checking_function is so complicated that it is not workaroundable
with other tools. i wanted to create this situation by introducing the
funny constraint that the sum of the number of rows in the two tables is
even. of course in my real db the constraint is a more useful one...


cu
erik

-- 
Erik Thiele
Horst Thiele Maschinenbau-Hydraulische Geräte GmbH
Im Kampfrad 2 - 74196 Neuenstadt
Tel.: 07139/4801-19
Fax.: 07139/4801-29
email: [email protected]
Internet: http://www.thiele-hydraulik.de/

view thread (13+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: special integrity constraints
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox