A way to add a constraint between two domains in a table without using a trigger?

Question

I have a following table, Client.

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

Client can have either TaxID or SSN or both. But either one should exist.
Currently, I am enforcing the rule thru 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 to enforce the rule?

Answer 1

You should be able to create a Check Constraint, on the Client table, to do this.

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

Answer 2

A CHECK constraint that checks for null with a boolean OR would seem to do the trick.

Answer 3

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

Answer 4

You can create a Check constraint that checks for null in either field:

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