Re: Conditional NOT NULL constraint

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