Posted by Erland Sommarskog on 06/10/07 18:11

Helen Wheels (helenwheelss@yahoo.com.au) writes:
> Can we use parentheses in a check constraint in MS-SQL-server DDL?
>
> e.g. I'm having a problem with the following statement:
>
> ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [CK_MyTable_TimesDataOK]
> CHECK (([TimeOn] IS NULL AND [TimeOff] IS NULL)
> OR
> ([ShiftCode] IS NOT NULL AND [TimeOn] IS NOT NULL AND [TimeOff] IS
> NOT NULL));
>
> The statement appears to run fine, but when I look at my table
> definition afterwards, it appears that SQL-server ignored the
> parentheses in my constraint; it shows the constraint expression as:
> (([TimeOn] IS NULL AND [TimeOff] IS NULL OR [ShiftCode] IS NOT NULL AND
> [TimeOn] IS NOT NULL AND [TimeOff] IS NOT NULL))

SQL Server does not store the constraint text as provided, but performs
some normalisations on it. One such normalisation is apparently to
remove superfluous parantheses. Your original constraint text and what
SQL Server saved, are equivalent. AND binds tighter than OR, so these
two are the same:

x AND y OR a AND b
(x ANY y) OR (a AND b)


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]