Re: special integrity constraints

From: Erik Thiele <erik(at)thiele-hydraulik(dot)de>
To: Bruno Wolff III <bruno(at)wolff(dot)to>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: special integrity constraints
Date: 2004-03-22 13:10:42
Message-ID: 20040322141042.0aeb936d.erik@thiele-hydraulik.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-docs pgsql-sql

On Mon, 22 Mar 2004 05:26:04 -0600
Bruno Wolff III <bruno(at)wolff(dot)to> wrote:

> On Mon, Mar 22, 2004 at 10:20:31 +0100,
> Erik Thiele <erik(at)thiele-hydraulik(dot)de> 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: erik(at)thiele-hydraulik(dot)de
Internet: http://www.thiele-hydraulik.de/

In response to

Responses

Browse pgsql-docs by date

  From Date Subject
Next Message Stephan Szabo 2004-03-22 14:21:28 Re: special integrity constraints
Previous Message Achilleus Mantzios 2004-03-22 11:40:11 Re: special integrity constraints

Browse pgsql-sql by date

  From Date Subject
Next Message Martin Marques 2004-03-22 13:14:40 Re: inverse of "day of year"
Previous Message Bruno Wolff III 2004-03-22 12:50:54 Re: inverse of "day of year"