Re: Constraint that compares and limits field values

Richard,

I have taken your suggestion and changed the values to NULL for the empty
foreign keys. Thank you for the constraint. I modified it to check for
NULL and it works great.

ALTER TABLE event
ADD CONSTRAINT two_nulls_1 CHECK (((((evenid1 IS NULL) AND (evevid1 IS
NULL)) OR ((evevid1 IS NULL) AND (evreid1 IS NULL))) OR ((evenid1 IS NULL)
AND (evreid1 IS NULL))));

ALTER TABLE event
ADD CONSTRAINT two_nulls_2 CHECK (((((evenid2 IS NULL) AND (evevid2 IS
NULL)) OR ((evevid2 IS NULL) AND (evreid2 IS NULL))) OR ((evenid2 IS NULL)
AND (evreid2 IS NULL))));

*** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** ***
*** ***
Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297

This e-mail message and any attachment(s) are for the sole use of the
intended recipient(s) and may contain proprietary and/or confidential
information which may be privileged or otherwise protected from
disclosure. Any unauthorized review, use, disclosure or distribution is
prohibited. If you are not the intended recipient(s), please contact the
sender by reply email and destroy the original message and any copies of
the message as well as any attachment(s) to the original message.

Richard Huxton <dev(at)archonet(dot)com>
01/25/2006 01:33 AM

To
MargaretGillon(at)chromalloy(dot)com
cc
pgsql-general(at)postgresql(dot)org
Subject
Re: [GENERAL] Constraint that compares and limits field values

MargaretGillon(at)chromalloy(dot)com wrote:
> I have a table that I am using to hold keys for M:M relationships. I
> have six fields that can hold the keys and I do this because I validate
> the key with a foreign key constraint. Fields evevid1, evevid2 hold keys

> from the event table, evreid1, evreid2 hold keys from the resource
table,
> etc. The 0 works with the FK constraints because in each table being
> referenced I have a record with id = 0 that is empty. Each row should
> only have two foreign key values and the other key field values are 0.
>
> How do I put a constraint on the Insert / Update to make sure that only
> two fields out of the six have a value > 0 in them.

Are you sure you don't want NULL rather than a fake row?

You can do the tests with a check constraint, although it'll look a bit
clumsy. Here's a simplified example that ensures two zeroes per row.

CREATE TABLE foo (a int, b int, c int);
ALTER TABLE foo ADD CONSTRAINT two_zeroes CHECK ((a=0 AND b=0) OR (b=0
AND c=0) OR (a=0 AND c=0));

INSERT INTO foo VALUES (1,0,0);
INSERT INTO foo VALUES (0,1,0);
INSERT INTO foo VALUES (0,1,1);
ERROR: new row for relation "foo" violates check constraint "two_zeroes"

I think you probably want to use null for foreign-keys that aren't
referencing anything though.
--
Richard Huxton
Archonet Ltd