[EMAIL PROTECTED] 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
.
Richard Huxton dev@archonet.com
01/25/2006 01:33 AM
To
[EMAIL PROTECTED]
cc
pgsql-general@postgresql.org
Subject
Re: [GENERAL] Constraint
that compares and limits field values
[EMAIL PROTECTED] wrote:
I have a table that I am using to hold keys for M:M relationships.
I
have
On Wed, Jan 25, 2006 at 08:39:00AM -0800, [EMAIL PROTECTED] 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
On Wed, Jan 25, 2006 at 09:55:58AM -0800, [EMAIL PROTECTED] 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
On Wed, Jan 25, 2006 at 09:55:58AM -0800, [EMAIL PROTECTED] 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
Michael Fuhr [EMAIL PROTECTED] 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
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, [EMAIL PROTECTED]
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
On Wed, Jan 25, 2006 at 10:51:23AM -0800, [EMAIL PROTECTED] 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.
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