Re: 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