开发者

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

开发者 https://www.devze.com 2022-12-20 17:16 出处:网络
I have a following table, Client. create table Client ( ClientIDint identity primary key, TaxIDvarchar(12),

I have a following table, Client.

create table Client (
    ClientID    int identity primary key,
    TaxID       varchar(12),
    SSN         varc开发者_StackOverflow社区har(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?


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


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


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


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)
0

精彩评论

暂无评论...
验证码 换一张
取 消