CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Jodrell
Jodrell

Reputation: 35716

Logical XOR and NULL checks

I have this check constraint,

CONSTRAINT [CK_VerboseXNOR] CHECK
    (([A] IS NULL AND [B] IS NULL) OR ([A] IS NOT NULL AND [B] IS NOT NULL))

it asserts that either A and B are both NULL or that both are not NULL. What you may consider an XNOR operation.

Is there a more succinct way to write this in TSQL that avoids the double use of each term.

Ideally I'd like this (you can't stop me dreaming.)

CONSTRAINT [CK_SuccinctXNOR] CHECK ([A] IS NULL XNOR [B] IS NULL)

Edit

I have tried

CHECK (CASE WHEN [A] IS NULL THEN [B] IS NULL ELSE [B] IS NOT NULL END)

Which looks like just three evaluations but, it doesen't work (won't parse).

I haven't tried

CHECK (IIF([A] IS NULL, [B] IS NULL, [B] IS NOT NULL))

and given the failure of above, I'm not optimistic enough to try.

I can't quite get my head around how CHECK can accept a logical expression but CASE (and perahps IIF) cannot return one.

Upvotes: 2

Views: 671

Answers (2)

usr
usr

Reputation: 171206

XNOR is just equals. XOR is not equals.

Because there is no boolean type in T-SQL (for no good reason) we have to abuse integers:

WHERE
 (CASE WHEN [A] IS NULL THEN 1 ELSE 0 END)
 = (CASE WHEN [B] IS NULL THEN 1 ELSE 0 END)

Not pretty either.

Martin's variant is slightly better:

WHERE IIF(A IS NULL, 0, 1) = IIF(B IS NULL, 0, 1)

I usually use the form that you have written in your question. The optimizer recognizes this form (for example for index seeks) but it does not recognize the integer-based form.

Upvotes: 3

Peter M
Peter M

Reputation: 7493

How about writing a function that does your XNOR and calling that in your constraint?

I have never down this myself, but google popped up: Check Constraints That Call Functions

Upvotes: 0

Related Questions