Posted by Chris.Cheney on 06/10/07 10:17

Helen Wheels <helenwheelss@yahoo.com.au> wrote in
news:136mli9pi74bs63@corp.supernews.com:

> 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))
>
> My intention is that if there's (non null) data in either of the
> columns TimeOn or TimeOff is not null, all three of the columns
> TimeOn, TimeOff and ShiftCode must have non null data.
>
> OK, I realise I could enforce this by altering my table setup in other
> ways. Right now I'm just trying to figure out if this I'm just up
> against a difference between dialects of SQL in check constraints
> here. Am I missing something obvious with parentheses?
>
> BTW the DDL for the table I'm testing on:
> CREATE TABLE [dbo].[MyTable](
> [FNname] [nvarchar](50) NOT NULL,
> [ShiftDate] [datetime] NOT NULL,
> [ShiftCode] [nchar](2) NULL,
> [TimeOn] [nchar](4) NULL,
> [TimeOff] [nchar](4) NULL);
>

"When more than one logical operator is used in a statement, the AND
operators are evaluated first ..." (BOL) - your inner parentheses are
therefore unnecessary.

[Back to original message]