CODEWITHSUNDEEP
sqlsql-server-2012
FistOfFury
FistOfFury

Reputation: 7125

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?

Upvotes: 39

Views: 23783

Answers (3)

Filip De Vos
Filip De Vos

Reputation: 11908

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

Upvotes: 54

Reinier Garcia
Reinier Garcia

Reputation: 1680

Another option:

CONSTRAINT at_least_one_not_null CHECK (
            COALESCE((col1)::BOOLEAN::INTEGER, 0) +
            COALESCE((col2)::BOOLEAN::INTEGER, 0) > 0
    )

Upvotes: 0

nahab
nahab

Reputation: 1354

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 )

Upvotes: 31

Related Questions