SQL Constraint conditional Check: if column0 is null, column1 is not null and vice versa

Question

I have this question around SQL constraints, in order to achieve following behavior:

TableA has two columns column0 and column1, which only one can be NULL on data entry: e.g.: if column0 is null, column1 can't be null if column1 is null, column0 can't be null

To achieve this, I've build following SQL constraints:

CONSTRAINT column01_not_null_chk
CHECK (
  ( column0 IS NOT NULL
  AND column1 IS NULL )
OR
  ( column1 IS NOT NULL
  AND column0 IS NULL ) )

is this correct to achieve my behavior? Because all SQL are rejected because of this constraint

Which RDBMS is this for? Please add a tag to specify whether you're using mysql, postgresql, sql-server, oracle or db2 - or something else entirely. — marc_s
– marc_s, Commented Apr 5, 2017 at 16:25
This do not only check to not have two nulls, this force to have one and only one null. — iguypouf
– iguypouf, Commented Apr 5, 2017 at 16:27
@iguypouf this is meant to be. Only one of them (column0 or column1) must be not null — meetnick
– meetnick, Commented Apr 6, 2017 at 10:51

z m · Accepted Answer · 2017-04-05 16:31:46Z

2

The condition you described means that at least one of them has to have value. So your constraint should be corrected to:

CONSTRAINT column01_not_null_chk
CHECK (column0 IS NOT NULL OR column1 IS NOT NULL)

answered Apr 5, 2017 at 16:31

z m

1,5132 gold badges21 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SqlZim Over a year ago

Good catch! The question didn't say that two non null values should fail.

meetnick Over a year ago

@Zack what a dumb I am! I should have simplify my logic, through this way. Is better for reading! Thx

SqlZim · Accepted Answer · 2017-04-05 16:27:50Z

1

Seems to work fine on SQL Server:

create table t (
     column0 int null
   , column1 int null
, CONSTRAINT column01_not_null_chk
CHECK (
  ( column0 IS NOT NULL
  AND column1 IS NULL )
OR
  ( column1 IS NOT NULL
  AND column0 IS NULL ) )
);

insert into t values (null,1),(1,null);

select * from t;
--insert into t values (null,null) /* fail */
--insert into t values (1,1) /* fail */

rextester demo: http://rextester.com/OQZNE39497

returns:

+---------+---------+
| column0 | column1 |
+---------+---------+
| NULL    | 1       |
| 1       | NULL    |
+---------+---------+

answered Apr 5, 2017 at 16:27

SqlZim

38.2k6 gold badges45 silver badges61 bronze badges

1 Comment

meetnick Over a year ago

Thank you for mentioning rextester tool @SqlZim, I was having other problem resulting in failure

Collectives™ on Stack Overflow

SQL Constraint conditional Check: if column0 is null, column1 is not null and vice versa

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related