From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | MargaretGillon(at)chromalloy(dot)com |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Constraint that compares and limits field values |
Date: | 2006-01-25 09:33:18 |
Message-ID: | 43D745DE.7080309@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2006-01-25 09:36:20 | Re: Shared Database across multiple servers using OCFS2 |
Previous Message | Markus Schaber | 2006-01-25 09:27:24 | Re: [SQL] hi all......................!! |