Check constraint

On Wed, 17 Mar 2004, Stephan Szabo wrote:

Actually, shouldn't a table level check constraint be able to do this with
something like:
check (purchase_type!=3 or comment is not null)

That worked Stephan.

Gregory. I think yours would work too. Saw Stephans answer and tested
before I saw your email.

Thanks guys.

On Wednesday 17 March 2004 12:03, Francisco Reyes wrote:

I have a "comment" field in a table that I want populated if another field
has a certain value. Is it possible to set a check constraint for this?

Example:
Let's say we have fields
Purchase_type smallint check(purchase_type <4)
comment varchar

I need a check rule to something like (pseudo code)
check (if purchase_type = 3 then NOT NULL comment)

No - you'll want a BEFORE UPDATE/INSERT trigger. It shouldn't be difficult to
do though.

--
Richard Huxton
Archonet Ltd

On Wed, 17 Mar 2004, Richard Huxton wrote:

On Wednesday 17 March 2004 12:03, Francisco Reyes wrote:

I have a "comment" field in a table that I want populated if another field
has a certain value. Is it possible to set a check constraint for this?

Example:
Let's say we have fields
Purchase_type smallint check(purchase_type <4)
comment varchar

I need a check rule to something like (pseudo code)
check (if purchase_type = 3 then NOT NULL comment)

No - you'll want a BEFORE UPDATE/INSERT trigger. It shouldn't be difficult to
do though.

Actually, shouldn't a table level check constraint be able to do this with
something like:
check (purchase_type!=3 or comment is not null)

I have a "comment" field in a table that I want populated if another field
has a certain value. Is it possible to set a check constraint for this?

Example:
Let's say we have fields
Purchase_type smallint check(purchase_type <4)
comment varchar

I need a check rule to something like (pseudo code)
check (if purchase_type = 3 then NOT NULL comment)

No - you'll want a BEFORE UPDATE/INSERT trigger. It shouldn't be difficult to
do though.

I don't see why not:

CHECK ((purchase_type <> 3) OR ((purchase_type = 3) AND comment IS NOT
NULL))

On Wed, Mar 17, 2004 at 12:03:04 +0000,
Francisco Reyes <lists@natserv.com> wrote:

I have a "comment" field in a table that I want populated if another field
has a certain value. Is it possible to set a check constraint for this?

Example:
Let's say we have fields
Purchase_type smallint check(purchase_type <4)
comment varchar

I need a check rule to something like (pseudo code)
check (if purchase_type = 3 then NOT NULL comment)

As long as the fields are in the same table you can do this. But you have
to use the IS NOT NULL function rather than try to activate a NOT NULL
constraint. The check would look something like:
check (purchase_type <> 3 OR IS NOT NULL comment)

On Wednesday 17 March 2004 17:30, Gregory Wood wrote:

I have a "comment" field in a table that I want populated if another
field has a certain value. Is it possible to set a check constraint for
this?

Example:
Let's say we have fields
Purchase_type smallint check(purchase_type <4)
comment varchar

I need a check rule to something like (pseudo code)
check (if purchase_type = 3 then NOT NULL comment)

No - you'll want a BEFORE UPDATE/INSERT trigger. It shouldn't be
difficult to do though.

I don't see why not:

CHECK ((purchase_type <> 3) OR ((purchase_type = 3) AND comment IS NOT
NULL))

Ah - I misread the original post. I thought Francisco was trying to
automatically copy the comment field when purchase_type=3.

Apologies, Francisco

--
Richard Huxton
Archonet Ltd