Обсуждение: Constraint that compares and limits field values

MargaretGillon@chromalloy.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

On Wed, Jan 25, 2006 at 08:39:00AM -0800, MargaretGillon@chromalloy.com wrote:
> 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))));

Is there a requirement that exactly one column be NOT NULL?  If so
then you'll need to add a check for that because this constraint
would allow all three to be NULL.

--
Michael Fuhr

On Wed, Jan 25, 2006 at 09:55:58AM -0800, MargaretGillon@chromalloy.com wrote:
> ALTER TABLE event
> ADD CONSTRAINT two_nulls_1 CHECK
> ((evenid1 IS NULL) AND (evevid1 IS NULL) and (evreid1 IS NOT NULL)) OR
> ((evenid1 IS NULL) AND (evevid1 IS NOT NULL) and (evreid1 IS NULL)) OR
> ((evenid1 IS NOT NULL) AND (evevid1 IS NULL) and (evreid1 IS  NULL))

Parhaps something like:

CHECK((CASE WHEN evenid1 IS NOT NULL THEN 1 ELSE 0 END) +
      (CASE WHEN evenid2 IS NOT NULL THEN 1 ELSE 0 END) +
      (CASE WHEN evenid3 IS NOT NULL THEN 1 ELSE 0 END)) = 1;

If you can find a function to turn a bool into an int it becomes even
easier.

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

On Wed, Jan 25, 2006 at 09:55:58AM -0800, MargaretGillon@chromalloy.com wrote:
> You are correct, in each group of three columns, one needs to have an
> integer and the other two need to be NULL. So I need to modify the
> constraint to be....
>
> ALTER TABLE event
> ADD CONSTRAINT two_nulls_1 CHECK
> ((evenid1 IS NULL) AND (evevid1 IS NULL) and (evreid1 IS NOT NULL)) OR
> ((evenid1 IS NULL) AND (evevid1 IS NOT NULL) and (evreid1 IS NULL)) OR
> ((evenid1 IS NOT NULL) AND (evevid1 IS NULL) and (evreid1 IS  NULL))
>
> Correct?

That looks right, aside from a syntax error from not having parentheses
around the entire expression.

CREATE TABLE event (
    id       serial PRIMARY KEY,
    evenid1  integer,
    evevid1  integer,
    evreid1  integer
);

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

INSERT INTO event (evenid1, evevid1, evreid1) VALUES (NULL, NULL, NULL);
INSERT INTO event (evenid1, evevid1, evreid1) VALUES (NULL, NULL, 1);
INSERT INTO event (evenid1, evevid1, evreid1) VALUES (NULL, 1, NULL);
INSERT INTO event (evenid1, evevid1, evreid1) VALUES (NULL, 1, 1);
INSERT INTO event (evenid1, evevid1, evreid1) VALUES (1, NULL, NULL);
INSERT INTO event (evenid1, evevid1, evreid1) VALUES (1, NULL, 1);
INSERT INTO event (evenid1, evevid1, evreid1) VALUES (1, 1, NULL);
INSERT INTO event (evenid1, evevid1, evreid1) VALUES (1, 1, 1);

SELECT * FROM event;
 id | evenid1 | evevid1 | evreid1
----+---------+---------+---------
  2 |         |         |       1
  3 |         |       1 |
  5 |       1 |         |
(3 rows)

In 8.1, and in earlier versions if you create a cast from boolean
to integer, you could do this:

ALTER TABLE event
ADD CONSTRAINT two_nulls_1 CHECK (
(evenid1 IS NOT NULL)::int +
(evevid1 IS NOT NULL)::int +
(evreid1 IS NOT NULL)::int = 1);

This works because the cast converts true to 1 and false to 0;
you're adding up the number of true expressions and requiring that
the sum equal 1 (i.e., that exactly one expression be true).

--
Michael Fuhr

On Wed, Jan 25, 2006 at 10:51:23AM -0800, MargaretGillon@chromalloy.com wrote:
> I am in version 7.3 and it will not let me cast, I get message "ERROR:
> Cannot cast type boolean to integer". I will save this for when I upgrade.

You can create casts with CREATE CAST.

http://www.postgresql.org/docs/7.3/static/sql-createcast.html

Something like this should work:

CREATE FUNCTION bool2int(boolean) RETURNS integer AS '
SELECT CASE WHEN $1 THEN 1 ELSE 0 END;
' LANGUAGE sql IMMUTABLE STRICT;

CREATE CAST (boolean AS integer) WITH FUNCTION bool2int(boolean);

--
Michael Fuhr