Search code examples
sqlsql-server-2012

I need a check constraint on two columns, at least one must be not null

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?

Solution

  • 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