11

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.

15

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) )
);
| improve this answer | |
  • Thanks for your reply. Yes columns are varchar. How do i check for empty values pls? – Kevin Nov 9 '17 at 8:44
  • @Kevin: see my edit – a_horse_with_no_name Nov 9 '17 at 8:47
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)); 
| improve this answer | |
1

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
)
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.