Hi Forum,
I am trying to create a check constraint that checks that both EntityIDPerson and EntityIDCorp are not null.
There are three conditions that can be met as per the code below. ie.
1) Both EntityIDPerson and EntityIDCorp have a value.
2) EntityIDPerson has a value while EntityIDCorp is NULL.
3) EntityIDPerson is NULL while EntityIDCorp has a value.
However, if EntityIDPerson is NULL AND EntityIDCorp is Null then it should fail.
The check constraint below works. However, is there a simpler, cleaner way to write the constraint?
Code:
CREATE TABLE Contact (
IDContact INT IDENTITY(1,1) NOT NULL,
EntityIDPerson INT NULL,
EntityIDCorp INT NULL,
ContactTypeID INT NOT NULL,
CONSTRAINT pk_Contact PRIMARY KEY (IDContact),
CONSTRAINT ck_Contact_EntityID CHECK
(EntityIDPerson IS NOT NULL AND EntityIDCorp IS NOT NULL
OR EntityIDPerson IS NOT NULL AND EntityIDCorp IS NULL
OR EntityIDPerson IS NULL AND EntityIDCorp IS NOT NULL)
);
dBforums
>
Help with Constraint : Check two Columns NOT NULL
04-10-10, 06:01
Linear Mode
