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.
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) ) );
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 )