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@xxxxxxxxxxxx>
01/25/2006 01:33 AM |
|
MargaretGillon@xxxxxxxxxxxxxx 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
