Burzum Burzum - 2 months ago 7
SQL Question

Add constraint based on two columns

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:


  1. Phone number is required if extension is not provided.

  2. Extension is required if phone number is not provided.

  3. 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

Doesn't your last clause has to be

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