Sql Server - Constraint - allow setting column A only if column B is zero and vice versa

Question

Is there a way to add a constraint in SQL Server 2008 that will verify that if a user tries to enter a value in column A, it can only be if column B is null and vice versa

Example

• if A is NULL, B may matter
• if B iS NULL, A may be set to
• A and B cannot matter at the same time

Answer 1

Something like that:

ALTER TABLE foo WITH CHECK ADD
    CONSTRAINT CK_Foo_reason CHECK (
        ColA IS NOT NULL AND ColB IS NULL
        OR
        ColA IS NULL AND ColB IS NOT NULL
        )

Edit: after updating the question

It depends on whether both NULL columns are allowed, in this case

        ColA IS NULL OR ColB IS NULL

Strike>

2: 3 , NOT NULL

, , . ,

ALTER TABLE foo WITH CHECK ADD
    CONSTRAINT CK_Foo_reason CHECK (
        ColA IS NOT NULL AND ColB IS NULL     AND ColC IS NULL
        OR
        ColA IS NULL     AND ColB IS NOT NULL AND ColC IS NULL
        OR
        ColA IS NULL     AND ColB IS NULL     AND ColC IS NOT NULL
        )

Answer 2

, :

CREATE TABLE dbo.Test_Constraint
(
    a INT NULL,
    b VARCHAR(10) NULL,
    CONSTRAINT Test_Constraint_CHK CHECK (a IS NULL OR b IS NULL)
)