Add constraint based on two columns

Question

I have a table called

Phone

that has two columns

Number varchar(32)

and

Extension varchar(32)

. I'd like to add a

Constraint

with the following criteria:

Phone number is required if extension is not provided.

Extension is required if phone number is not provided.

Both phone number and extension can be provided.

I added the following

Constraint

to meet all three requirements:

ALTER TABLE Phone

ADD CONSTRAINT RequirePhone

CHECK (

    Number IS NULL AND EXTENSION IS NOT NULL

    OR (Number IS NOT NULL AND Extension IS NULL)

);

The above

constraint

meets the first two requirements, however, if I provide

Number

and

Extension

I get constraint exception.

I have tried adding:

...

OR (Number IS NULL AND Extension IS NULL)

But I still get the same error.

Any suggestions on how I can resolve this?

Answer 1

Doesn't your last clause has to be

    OR (Number is NOT NULL AND Extension IS NOT NULL)

Add constraint based on two columns

Sign up