How to add a restriction between two domains in a table without using a trigger?

Question

I have the following table, Client .

 create table Client ( ClientID int identity primary key, TaxID varchar(12), SSN varchar(12) ) GO 

A client can have either TaxID or SSN, or both. But anyone must exist.
I am currently applying the rule through the following trigger.

 create trigger trgClient_UniqueTaxIDSSN on Client after Insert, Update as --; Check if either TaxID or SSN is not null. 

But is there a way to declare a constraint for enforcing a rule?

Answer 1

 ALTER TABLE Client ADD CONSTRAINT ck_TaxIDorSSN CHECK (TaxID is not null or SSN is not null) 

Answer 2

To do this, you can create a checksum in the customer table.

 http://msdn.microsoft.com/en-us/library/ms188258.aspx 

Answer 3

A CHECK constraint that checks for null using a logical OR seems to do the trick.

Answer 4

You can create a Check constraint that checks the null value in any of the fields:

 ALTER TABLE Client ADD CONSTRAINT cn_TaxIDorSSNnotNULL CHECK (TaxID IS NOT NULL OR SSN IS NOT NULL)