29

I have a table in SQL Server with two numeric columns. At least one of these numeric fields must be filled. How do I write a check constraint to verify this?

FistOfFury
  • 6,157
  • 6
  • 45
  • 55
  • 1
    Possible duplicate of [One of the column between two columns should be NOT NULL. How to enforce it in schema?](http://stackoverflow.com/questions/7017871/one-of-the-column-between-two-columns-should-be-not-null-how-to-enforce-it-in-s) – JohnnyHK Mar 10 '16 at 00:16
  • 2
    @JohnnyHK not duplicate as this question is about 'at least one of two' and that one is about 'only one of two' – nahab Mar 15 '17 at 10:46

2 Answers2

39

This can be done with a check constraint that verifies null value and matches the result with or

create table #t (i int
               , j int
               , constraint chk_null check (i is not null or j is not null))

The following are the test cases

insert into #t values (null, null) --> error
insert into #t values (1, null) --> ok
insert into #t values (null, 1) --> ok
insert into #t values (1, 1) --> ok
Filip De Vos
  • 11,248
  • 1
  • 46
  • 60
23

late answer, but here is a solution for Sql Server for any number of columns to check:

CONSTRAINT CK_one_is_not_null CHECK (COALESCE(col1, col2, col3) IS NOT NULL )
nahab
  • 1,248
  • 16
  • 35
  • This works if the columns have the same datatype. Otherwise the system will try to convert in order to check this constraint – GôTô Jul 23 '21 at 19:31