Check constraint to ensure both or neither values 1

[bawtry]

Given a table tblABC, I want to ensure that if a value is entered in field fld1 it is between 60 and 240 and that a value between 0 and 100 is entered in field fld2, or no value is entered for either field fld1 or field fld2. In other words both or neither.

I have tried this:

ALTER TABLE tblABC
ADD CONSTRAINT CK1tblABC CHECK (((fld1 IS NULL) AND (fld2 IS NULL)) OR ((fld1 between 60 AND 240) AND (fld2 between 0 AND 100)))

and this:

ALTER TABLE tblABC
ADD CONSTRAINT CK1tblABC CHECK (((fld1 IS NULL) AND (fld2 IS NULL)) OR ((fld1 >= 60 AND fld1 <= 240) AND (fld2 >= 0 AND fld2 <= 100)))

but neither constraint works. Both allow a value between 60 and 240 in fld1 and a null value in fld2. In both cases SQL Server rearranges the bracketing.

Any ideas as to how to phrase the TSQL for the constraint?

 

[karluk]

I am not sure why your constraint doesn't work. However, I find that the following rewrite does what you want.

ALTER TABLE test_ABC
ADD CONSTRAINT CK1tblABC CHECK ((fld1 IS NULL AND fld2 IS NULL)
OR (fld1 is not null and fld2 is not null and fld1 between 60 AND 240) AND (fld2 between 0 AND 100))

 

[karluk]

Sorry, I messed up the parentheses. Please try the following correction.

ALTER TABLE tblABC
ADD CONSTRAINT CK1tblABC CHECK ((fld1 IS NULL AND fld2 IS NULL)
OR (fld1 is not null and fld2 is not null and (fld1 between 60 AND 240) AND (fld2 between 0 AND 100)))
go

 

[bawtry]

Thanks, that works as desired.

I would like to understand why the first examples don't work, and why SQL Server rearranges the statements in the forms it does. Can anyone explain?

 

[karluk]

I'm fairly sure I know why your original check constraint wasn't working. It has nothing to do with SQL Server rearranging the parentheses. Rather, it's because nulls in an expression like &quot;fld1 between 60 AND 240&quot; evaluate to neither true nor false.

Suppose I have a table, test_null, with columns &quot;fld1&quot; and &quot;fld2&quot;. Given the data

fld1 fld2
70 50
null 60
80 null
500 30

The query

select * from test_null where (fld1 between 60 AND 240 AND fld2 between 0 AND 100)

returns the single row 70, 50.

On the other hand, the negation of this query

select * from test_null where not (fld1 between 60 AND 240 AND fld2 between 0 AND 100)

returns only the row 500,30.

Please notice that no rows with null in either column are selected by either the original where clause or its negation. That's because a null is an unknown value, so SQL Server can't tell whether it satisfies the between condition or not.

That's what's happening when your check condition gets invoked. The check condition rejects only those rows where the check condition evaluates to false. Since rows with nulls evaluate to &quot;unknown&quot; rather than &quot;false&quot;, the check constraint isn't violated and the rows can be inserted.

 

[bawtry]

You're right, and that would imply that the check constraint could be written:

CHECK (fld1 is null and fld2 is null or isnull(fld1,-1) between 60 and 240 and (isnull(fld2,-1) between 0 and 100))

This does indeed work.

Thanks for your help
Graham

 

[koyot20]

Hm, how to make a check constraint for this:
In a column can be just numbers, &quot;+&quot; or &quot;/&quot;.
I tried to check all the fields, but then the varchar column is as normal char.

 

[rt63]

check (isnumeric(replace(replace(field1,'+',''),'/','')) = 1
and charindex('.',field1) = 0 ))
RT

 

[koyot20]

Thanks rt63
Hm, I don't understand it well.
What this does exactly?

 

[rt63]

While checking, it removes + and / from the field and checks that the remaining characters form a valid numeric value. Since 12345.67 will also be a valid numeric value, it also checks that there is no . in the field.
RT

 

[koyot20]

rt63
Now its clear, thanks. Hm, any idea how to let the + just at the first character?

 

[koyot20]

rt63
Now it's clear. Hm, any ide how to let + to be just as the first character?

 

[rt63]

Assuming field1 is varchar(1000)

check (isnumeric(replace(left(field1,1),'+','')+replace(substring(field1,2,999),'/','')) = 1
and substring(field1,2,999) not like '%[.+]%' )
RT