parentheses in a check constraint

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);

Re: parentheses in a check constraint

Helen Wheels 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.

Re: parentheses in a check constraint

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

Re: parentheses in a check constraint

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/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx