Posted by Helen Wheels on 06/10/07 01:39

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

[Back to original message]