Re: Conditional NOT NULL constraint

[Richard Huxton <dev(at)archonet(dot)com>]

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