Mutually Exclusive NULL

Question

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135382 More actions · Answer 1

should work like this :

create table t_muteNULL (col1 int identity (1,1) not null, col2 varchar(128) , col3 varchar(128))

go

alter table t_muteNULL

add constraint chkMuteNULL check (case when col2 is null and col3 is null then 0 else 1 end = 1)

go

insert into t_muteNULL (col2, col3) values ('a', null) -- OK

go

insert into t_muteNULL (col2, col3) values (null, 'b') -- OK

go

insert into t_muteNULL (col2, col3) values (null, null) -- error

go

-- now some updates ...

update t_muteNULL set col2 = null , col3 = 'c' where col1 = 1 -- OK

go

update t_muteNULL set col3 = null where col1 = 1 -- error

go

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

rockmoose SSCarpal Tunnel Points: 4601 More actions · Answer 2

If it is XOR you are after, you could modify alzdba constriant to:

alter table t_muteNULL

add constraint chkMuteNULL check ((case when col2 is null then 1 else 0 end) ^ (case when col3 is null then 1 else 0 end) = 1)

ie, One of the columns has to be null, but not both.

/rockmoose

You must unlearn what You have learnt

Justin-14543 SSCrazy Points: 2017 More actions · Answer 3

Thanks a lot for both replies.

Actually both replies helped me in different places. Thanks.

Give me a fish, you feed me for a day! Teach me to fish, you feed me for a life time.