CODEWITHSUNDEEP
sqlsql-serverconstraints
Melursus
Melursus

Reputation: 10578

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

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

Upvotes: 6

Views: 2679

Answers (2)

gbn
gbn

Reputation: 432210

Something like this:

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 question update

It depends if both columns are allowed NULL in which case

        ColA IS NULL OR ColB IS NULL

Edit 2: For 3 columns, exactly one of which must be NOT NULL

No smarty-pants answer I'm afraid. I've added spaces to hopefully make it clearer

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
        )

Upvotes: 8

Tom H
Tom H

Reputation: 47444

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)
)

Upvotes: 0

Related Questions