Message-ID: <45E5627B.5090603@archonet.com>
Date: Wed, 28 Feb 2007 11:07:39 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: IceDove 1.5.0.9 (X11/20061220)
MIME-Version: 1.0
To: Andreas Joseph Krogh <andreak@officenet.no>
Cc: pgsql-sql@postgresql.org
Subject: Re: Conditional NOT NULL constraint
References: <200702281130.47396.andreak@officenet.no>
In-Reply-To: <200702281130.47396.andreak@officenet.no>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Andreas Joseph Krogh wrote:
> Hi all!
> Is there a simple way to add a "NOT NULL constraint" to a column without using 
> a trigger if another column is not null?
> Something like this:
> 
> CREATE TABLE activity(
> id SERIAL primary key,
> name varchar not null,
> created timestamp not null default now(),
> modified timestamp,
> created_by integer not null,
> modified_by integer
> );
> 
> alter table activity alter column modified_by set not null where modified is 
> not null;
> 
> I want a constraint which says: "modified_by not null if modified is not 
> null".

Would a CHECK do? Something like:

ALTER TABLE activity ADD CONSTRAINT both_modified_set CHECK 
((modified_by IS NULL AND modified IS NULL) OR (modified_by IS NOT NULL 
AND modified IS NOT NULL))


-- 
   Richard Huxton
   Archonet Ltd