Adding Conditional Not Null Constraint to Table

Question

Adding Conditional Not Null Constraint to Table

VDT-7677 161

Hi,

Running SQL Server 2008 R2. DDL below:

CREATE TABLE [dbo].[Test]

(

[TestID] [bigint] NOT NULL IDENTITY,

[Column1] [varchar](50) NOT NULL,

[Column2] [varchar](50) NULL,

[Column3] [varchar](50) NULL,

[Flag] [bit] NOT NULL,

CONSTRAINT [PK_Test_TestID] PRIMARY KEY CLUSTERED ([TestID] ASC)

)

GO

ALTER TABLE [dbo].[Test] ADD CONSTRAINT [DF_Test_Flag] DEFAULT ((0)) FOR [Flag]

GO

How would I go about adding a constraint that satisfies the following conditions:

If Flag is set to 0 (or null for new rows that have yet to have the default constraint applied), then require non-empty values in Column2 and Column3,
If Flag is set to 1, then require null values in Column2 and Column3

I tried the constraint below but it's not working (preventing insert of row):

ALTER TABLE [dbo].[Test] WITH CHECK ADD CONSTRAINT [CN_Test_Flag] CHECK (ISNULL([Flag], 0) = 0 AND [Column2] IS NOT NULL AND [Column3] IS NOT NULL)

GO

Any thoughts?

Thanks!

Accepted answer

1 additional answer

Your answer

Answer 1

ZoeHui-MSFT 41,456

Hi @VDT-7677，

Try with below code.

ALTER TABLE [dbo].[Test] WITH CHECK ADD CONSTRAINT [CN_Test_Flag] CHECK (
    (Flag = 0 AND Column2 IS NOT NULL AND Column2 <> '' AND Column3 IS NOT NULL AND Column3 <> '') OR
    (Flag = 1 AND Column2 IS NULL AND Column3 IS NULL)
)
GO

Regards，

Zoe Hui

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

Sukrut parab 0 MVP

Use Not null in select from statement

VDT-7677 161 Reputation points

2025-03-25T23:11:32.7966667+00:00

Hi Sukrut,

Not sure what you mean. Could you elaborate on your answer?

Thanks!

Share via

Adding Conditional Not Null Constraint to Table

1 additional answer

Your answer