Search code examples
sqlsql-serversql-server-2008check-constraint

CHECK constraint not working

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

Solution

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