2

I have to create a check constraint on the table based on the below condition.

1. If ColumnX=1 then ColumnY should not be NULL

2. If ColumnX<>1 then ColumnY can be NULL

I tried below and seems to be not checking the value.

ALTER TABLE [dbo].[MyTable]  WITH NOCHECK ADD  CONSTRAINT [CK_MyTable_1] 

CHECK  (ColumnX<>1 OR(ColumnX=1 AND (ISNULL(ColumnY,0) <> 0)))
GO

ALTER TABLE [dbo].[MyTable] CHECK CONSTRAINT [CK_MyTable_1]
GO

Not sure whether the logic in the constraint is correct

Billa
  • 5,226
  • 23
  • 61
  • 105

2 Answers2

2

How about:

CHECK ( ColumnX <> 1 OR (ColumnX = 1 AND ColumnY is not null) )

If ColumnX can be NULL, you might want:

CHECK ( ColumnX <> 1 OR ColumnX is null OR (ColumnX = 1 AND ColumnY is not null) )
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • This `CHECK ( ColumnX <> 1 OR (ColumnX = 1 AND ColumnY is not null) )` is fine. I am not sure why my appoach is not working by checking ISNULL with 0 – Billa Aug 12 '14 at 13:22
0
ColumnX != 1 or ColumnY is not null
Roger Wolf
  • 7,307
  • 2
  • 24
  • 33