Check constraint on 2 fields
-
Field1 can be several values depending on the status of the record, C, X, B, R etc. However I don't want my users to change this record to a B unless the document field has a good (not dummy) number inserted. How do I write the check constraint?
I tried (field1 = 'B' and field2 <> 0000) But that doesn't allow my users to use the C, X or R values.
What am I missing?
Michelle -
try (field1 <> 'B' or field2<>0000)
-
Use IN operator
Create Table xXx
( Field1 vachar(10), Field2 varchar(10),
Check ( Field1 IN ('C', 'X', 'B', 'R') And
Field2 <> 0000 ))
-
If they choose C, X, or R then the field2 can be 0000. It is only when they choose B that field2 must be a real document number.
Thanks
Michelle -
try:
CHECK((Field1 IN ('C', 'X', 'R')) Or (
Field1 = 'B' AND Field2 <> 0000)) -
me thinks you did not try my first suggestion...?
>> (field1<>'B' or field2<>0000)
jpipes response is probably more understandalbe, but mine is shorter.
and it would allow you to add other codes that don't require a valid # in field2. then again, maybe I'm off my rocker and should actually try testing code suggestions before submitting them. -
Completely agree with joshcsmith13: his suggestion will be much more maintainable... 🙂
-
My apologies joshcsmith13, my tired brain read your suggestion as a way to never have the 'B' in field1. I am awake now and I understand it. I did use jpipes suggestion and that worked but I agree that I like joshcsmith13's better also.
Thank you everyone!
Michelle
and it would allow you to add other codes that don't require a valid # in field2. then again, maybe I'm off my rocker and should actually try testing code suggestions before submitting them. 
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply