Posted by Helen Wheels on 06/10/07 10:36

Chris.Cheney wrote:
> 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.

So they are. The constraint is working as expected, it just doesn't look
quite the way I'm used to reading it. Thanks.

[Back to original message]