Thread: Check constraint
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)
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
>>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 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 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)
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