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