Sql-server – SQL Check Constrain with ONLY IF statment

constraintdatabase-designsql server

I have a table with a check constraint that enforce a not null for the data column if the havedata column is true. is there away to modify the constraint to not allow case 3 to happen. i.e Data is not null Only if have data is true?

ID  HaveData   Data
1      0       Null
2      1       trg$%%D 3 0 %^&&FFF Best Answer You can achieve this by using an OR condition in your CHECK CONSTRAINT. Setup Table: CREATE TABLE dbo.CheckData ([ID] INT IDENTITY, [HaveData] BIT NOT NULL, [Data] NVARCHAR(25) NULL, CONSTRAINT CK_HaveData_Data CHECK (([HaveData] = 0 AND [Data] IS NULL) OR ([HaveData] = 1 AND [Data] IS NOT NULL)) ); Add Data: -- This works because Data is NULL and HaveData is 0 INSERT INTO CheckData ([HaveData], [Data]) VALUES (0, NULL) -- This works because Data is not NULL and HaveData is 1 INSERT INTO CheckData ([HaveData], [Data]) VALUES (1, 'trg$%%D')

-- This fails because Data is not NULL and HaveData is 0
INSERT INTO CheckData ([HaveData], [Data])
VALUES (0, '%^&&FFF')

You can see this in action in this db<>fiddle.