Check constraints not what you thought they were?

I was recently adding a check constraint to a table in SQL Server, and noted some behaviour which surprised me, so I thought I'd share it with you. I've tried this on SQL Server 2012 and 2008 R2.

I added two fields, which either both needed to be null or both filled in. (Now, it is possible this requirement points to a normalisation requirement - but that's not the point at hand). So the check constraint is simply to ensure that either:

• Field A is null AND Field B is null

OR

• Field A is not null AND Field B is not null 

So I used the following check constraint:


USE tempdb

CREATE TABLE AllOrNothing
    (
      id INT IDENTITY(1, 1) ,
      FieldA VARCHAR(100) ,
      FieldB VARCHAR(100)
    )

ALTER TABLE AllOrNothing ADD CONSTRAINT UK_AllOrNothing 
    CHECK (
             (FieldA IS NULL AND FieldB IS NULL) 
           OR 
             (FieldA IS NOT NULL AND FieldB IS NOT NULL)
           )


Now, what surprised me was when I queried sys.constraints on this:

and again if I modify the constraint in SQL Server Management Studio:

The brackets have gone! Now, this isn't going to affect the functionality due to the precedence order of the logical operators, but will still have a SQL Developer looking at it and wondering if the intended functionality is what's been put in place. (Of course, the best way to be sure is to unit test your code).

I thought I'd blog about this, as whilst it doesn't change the function of what's been coded, it does change the form of it, which is unusual and was to me unexpected.