2

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.

Jonathan Wood
  • 65,341
  • 71
  • 269
  • 466

2 Answers2

4

This works:

   (CompaniesId is null AND HotelsId is not null) 
OR (CompaniesId is not null AND HotelsId is null)
Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • I need to use this on a couple of tables and was looking for a more concise expression. Can you explain why my more concise version won't work? – Jonathan Wood May 04 '15 at 21:09
  • @JonathanWood: because it's not a valid syntax. But honestly i'm not sure where it is documented. `IS (NOT)` returns a `BOOLEAN` which cannot be used like in a programming language. It's valid f.e. in a `WHERE`. _"Unlike other SQL Server data types, a Boolean data type cannot be specified as the data type of a table column or variable, and cannot be returned in a result set."_ You can use `<>` or `!=` only with [expressions](https://msdn.microsoft.com/en-us/library/ms190286.aspx). – Tim Schmelter May 04 '15 at 21:20
  • Don't put too much focus on "conciseness". Sure when possible, it is better, but your focus you should be performance and accuracy. Tim's code will perform both of those well. As far as conciseness, it's not bad either. If you're worried for someone later using it and not knowing what's going on, well then just pop a comment in the code to tell that programmer what your code is doing. – Stephan May 04 '15 at 21:24
4

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.

usr
  • 168,620
  • 35
  • 240
  • 369
  • Obviously, I spend most of my time in languages other than SQL. – Jonathan Wood May 04 '15 at 21:26
  • Thumbs up for giving what the OP wanted, but it's really not recommended to perform functions on column in the where clause because of performance issues. – Stephan May 04 '15 at 21:29
  • 1
    @Stephan: Valid point, but this *was* the information I was after. I probably will always think in terms of conciseness because I have been using languages that like that for many, many, many years. But perhaps the longer version does make more sense here. (BTW, using `iif`SSMS converts it to a CASE statement.) – Jonathan Wood May 04 '15 at 21:33
  • 2
    @usr: `IS NULL` is not a function, `ISNULL` is one. Also, T-SQL _does_ have a boolean type and it's returned from [`IS (NOT) NULL`](https://msdn.microsoft.com/en-us/library/ms188795.aspx). But it cannot be used in that way. – Tim Schmelter May 04 '15 at 21:41
  • This is just semantics. It behaves like a function but has different notation. It might say "Boolean result" in the docs but it's a farce. What good is a type that you can't use? It's effectively not there. It exists merely as a textual statement in the docs. – usr May 04 '15 at 21:48
  • 1
    @usr: a function cannot make use of an index a predicate like `IS` can. You can use `boolean` but not as datatype for a column or variable but for the `WHERE`. May be nitpicking, just wanted to say that it exists because your first sentence argues the contrary.. – Tim Schmelter May 04 '15 at 22:39
  • Functions *can* make use of an index if the query processor understands them. IS NULL is a function to the query processor. The specific letters used to denote that function call are irrelevant to the defined mechanics. There was an example of a function that was SARGable but I can't find it right now. Anyway, I clarified the first sentence a bit. – usr May 05 '15 at 07:55