pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedConditional NOT NULL constraint
3+ messages / 2 participants
[nested] [flat]
* Conditional NOT NULL constraint
@ 2007-02-28 10:30 Andreas Joseph Krogh <andreak@officenet.no>
0 siblings, 1 reply; 3+ messages in thread
From: Andreas Joseph Krogh @ 2007-02-28 10:30 UTC (permalink / raw)
To: pgsql-sql
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".
I know I could use a trigger for this, but I'm curious if there is a way to do
this with an index.
--
Andreas Joseph Krogh <andreak@officenet.no>
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
------------------------+---------------------------------------------+
OfficeNet AS | The most difficult thing in the world is to |
Karenslyst Allé 11 | know how to do a thing and to watch |
PO. Box 529 Skøyen | somebody else doing it wrong, without |
0214 Oslo | comment. |
NORWAY | |
Mobile: +47 909 56 963 | |
------------------------+---------------------------------------------+
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Conditional NOT NULL constraint
@ 2007-02-28 11:07 Richard Huxton <dev@archonet.com>
parent: Andreas Joseph Krogh <andreak@officenet.no>
0 siblings, 1 reply; 3+ messages in thread
From: Richard Huxton @ 2007-02-28 11:07 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreak@officenet.no>; +Cc: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Conditional NOT NULL constraint
@ 2007-02-28 11:23 Andreas Joseph Krogh <andreak@officenet.no>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 3+ messages in thread
From: Andreas Joseph Krogh @ 2007-02-28 11:23 UTC (permalink / raw)
To: pgsql-sql; +Cc: Richard Huxton <dev@archonet.com>
On Wednesday 28 February 2007, Richard Huxton wrote:
> 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))
Certainly, thanks.
--
Andreas Joseph Krogh <andreak@officenet.no>
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
------------------------+---------------------------------------------+
OfficeNet AS | The most difficult thing in the world is to |
Karenslyst Allé 11 | know how to do a thing and to watch |
PO. Box 529 Skøyen | somebody else doing it wrong, without |
0214 Oslo | comment. |
NORWAY | |
Mobile: +47 909 56 963 | |
------------------------+---------------------------------------------+
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-02-28 11:23 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-02-28 10:30 Conditional NOT NULL constraint Andreas Joseph Krogh <andreak@officenet.no>
2007-02-28 11:07 ` Richard Huxton <dev@archonet.com>
2007-02-28 11:23 ` Andreas Joseph Krogh <andreak@officenet.no>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox