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

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@archonet.com>
01/25/2006 01:33 AM

To
MargaretGillon@chromalloy.com
cc
pgsql-general@postgresql.org
Subject
Re: [GENERAL] 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

Michael Fuhr wrote:
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.

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?
*** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** ***
*** ***
Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297

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/

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

Michael Fuhr <mike@fuhr.org> wrote on 01/25/2006 10:25:38 AM:
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

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.

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

Martijn van Oosterhout <kleptog@svana.org> wrote on 01/25/2006 10:20:40

AM:

On Wed, Jan 25, 2006 at 09:55:58AM -0800, MargaretGillon@chromalloy.com

wrote:

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;

This works with a few modifications... needed an extra ( ) enclosing
entire statement.

ALTER TABLE event
ADD CONSTRAINT two_nulls_1
CHECK (((CASE WHEN evenid1 IS NOT NULL THEN 1 ELSE 0 END) +
(CASE WHEN evevid1 IS NOT NULL THEN 1 ELSE 0 END) +
(CASE WHEN evreid1 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.

I am in version 7.3 and it will not let me cast the boolean to an integer.

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

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