CODEWITHSUNDEEP
sqlsql-serversql-server-2012
smr5
smr5

Reputation: 2793

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?

Upvotes: 0

Views: 54

Answers (2)

HansVG
HansVG

Reputation: 789

Doesn't your last clause has to be

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

Upvotes: 4

Serg
Serg

Reputation: 22811

(1)..(3) is equivalent to both NULLs is forbideen all the rest is OK.

ALTER TABLE Phone
ADD CONSTRAINT RequirePhone
   CHECK (Number IS NOT NULL OR Extension IS NOT NULL)
);

Upvotes: 1

Related Questions