0

I have a case where if the Id field is a specific value, I don't want to allow null in another field, but if the Id value <> a specific value, null is ok.

In the example below, inserting the first record should succeed, the second should succeed, and the 3rd should fail. Right now the 2nd two fail. I gotta be missing something easy, but I can't figure it out.

USE tempdb
GO

IF OBJECT_ID('tempdb.dbo.CheckConstraintTest') IS NOT NULL
    DROP TABLE  tempdb.dbo.CheckConstraintTest;
CREATE TABLE CheckConstraintTest
    (
      CTId INT NOT NULL ,
      Goal INT NULL ,
      CONSTRAINT CK_CheckConstraintTest_Goal CHECK ( CTId = 1
                                                     AND Goal IS NOT NULL )
    );

INSERT  INTO CheckConstraintTest
        ( CTId, Goal )
VALUES  ( 1, 0 );

INSERT  INTO CheckConstraintTest
        ( CTId, Goal )
VALUES  ( 2, NULL );

INSERT  INTO CheckConstraintTest
        ( CTId, Goal )
VALUES  ( 1, NULL );
sqlpadawan
  • 305
  • 1
  • 5
  • 15

1 Answers1

0
CHECK ( NOT (CTId = 1 AND Goal IS NULL) )
Anon
  • 10,660
  • 1
  • 29
  • 31