Check Constraint to Confirm Exactly One is not NULL

Question

I have a table with the columns CompaniesId and HotelsId. I want to write a check constraint that confirms that one of these columns is null and other one is not.

I tried the following expression:

(CompaniesId is null) <> (HotelsId is null)

When I attempt to save this in the Check Constraints dialog box in SSMS, I get the helpful message:

Error validating constraint 'CK_CostingLineItemVendors'.

Could someone help me with my syntax?

EDIT:

According to the article IS [NOT] NULL (Transact-SQL), the result of IS NULL or IS NOT NULL is Boolean. That's why I would assume I could compare these results for equality.

Answer 1

T-SQL does not have a usable boolean type. You can't have an expression that has boolean type and expect to use it like any other expression. All the places in T-SQL where you seemingly can use a boolean expression (like WHERE) are hard-coded places in the syntax. (Yes, this is a deficiency without specific reason.)

Simply abuse a bit or an int as a bool:

IIF(CompaniesId is null, 1, 0) <> IIF(HotelsId is null, 1, 0)

IIF is an intrinsic function that can take a condition as the first argument like a CASE can. You can't define such a function yourself.