Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Tricky logical XOR for nullable values

I have 2 nullable CHAR columns and I need to check if only one of them is null.

Doing

(a IS NULL AND b IS NOT NULL) OR (a IS NOT NULL AND b IS NULL)

is boring. And I would like to avoid creating custom functions for that.

I was thinking about something like

COALESCE(a, 1) + COALESCE(b, 1) = 1

but as long as a is char - it causes operand type error.

So, any tricky solutions?

like image 985
zerkms Avatar asked Apr 18 '12 03:04

zerkms


2 Answers

If you mean exactly one is NULL (which matches your existing logic), then:

a is null != b is null
like image 84
mu is too short Avatar answered Oct 04 '22 01:10

mu is too short


And if you're using PostgreSQL don't forget the parentheses...

ALTER TABLE "schema"."table" ADD CHECK ((key1 IS NULL) <> (key2 IS NULL));

(I spent almost 10 minutes trying to understand what was wrong with my CHECK.)

like image 34
Hugo Tavares Avatar answered Oct 04 '22 02:10

Hugo Tavares