Sql Server - Constraint - Allow to set column A only if column B is null and vice-versa

Question

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

Example

• if A is NULL, B can have a value
• if B iS NULL, A can have a value
• A and B can't have value at the same time

Answer 1

You can set up a trigger to check for this.

Answer 2

This seemed to work for me:

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