Create a Constraint such that only one of two fields must be filled

Question

I have a table in SQL server where the users need to enter data in either of two columns. That is, One of the two must have data inputted but at the same time i don't want to allow users to input in both columns. It's either or but one is a must.

Answer 1

You need to use a check constraint:

create table kevin
(
  one   integer, 
  other integer,
  constraint only_one_value 
        check (        (one is null or other is null) 
               and not (one is null and other is null) )
);

This ensures that at least one of the columns has a value and that not both have a value.

If those are varchar columns you might want to check for empty values ('') as well. To do that use nullif() which converts a value to null if it is equal to the second argument of the function.

create table kevin
(
  one   integer, 
  other integer,
  constraint only_one_value 
        check (        (nullif(one,'') is null or nullif(other,'') is null) 
               and not (nullif(one,'') is null and nullif(other,'') is null) )
);

Answer 2

Smth like this?

create table #t (col1 int, col2 int)
go

alter table #t with check add 
   constraint ck_only_one check ((col1 is null and col2 is not null) or (col2 is null and col1 is not null)); 

Answer 3

An approach that scales better to more than 2 columns is to check that the number of non-null columns is 1.

create table #t (col1 int, col2 int, col3 int)
go

alter table #t with check add constraint ck_only_one check (
    1 = case when col1 is null then 0 else 1 end
        + case when col2 is null then 0 else 1 end
        + case when col3 is null then 0 else 1 end
)