Re: Conditional NOT NULL constraint - Mailing list pgsql-sql

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