469,356 Members | 2,228 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,356 developers. It's quick & easy.

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);
Jun 10 '07 #1
3 5873
Helen Wheels <he**********@yahoo.com.auwrote in
news:13*************@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.
Jun 10 '07 #2
Chris.Cheney wrote:
Helen Wheels <he**********@yahoo.com.auwrote in
news:13*************@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.
Jun 10 '07 #3
Helen Wheels (he**********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 10 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Dylan Nicholson | last post: by
9 posts views Thread by Edmund Dengler | last post: by
1 post views Thread by huyuhui | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.