Understanding NULL(the ugly)

Welcome to part two of a two-part series explaining NULL in depth. You’re probably thinking “I already know about NULL. Why would I need to understand it? It’s just the absence of value”. And you’d be right on one thing, NULL does represent the absence of value but, there’s a lot more to it than that.

This article isn’t exclusive to DBAs or DB Developers, the topics covered in this series are important for Web/Software Developers as well. If somewhere down the road you end up feeling like:

dafuq did I just read?!

Don’t worry, everything will make sense in the end.

• Part one — covers the concept of NULL in relational database theory
• Part two — covers how NULL behaves in code

Strap yourselves in because, today we talk about the aspects of NULL that you really need to watch out for (and it’s going to be a long one). In the previous article, we discussed the relational database theory behind NULLs, today it’s all about how it behaves in code!

Unfortunately for John, his time in our experimentation lab isn’t over yet. Because, unlike the last time when we were making hypothetical scenarios, this time we will manually replace his appendages with real voids and empty spaces.

For now, however, John gets to rest a bit before his time on the chopping block, because we’re going to talk about CONSTRAINT. In SQL, constraints are an integral part of many databases, below you can find the list of different types of constraints we will be discussing today:

• Primary key (non-serial/non-sequence PK)
• Unique
• Foreign key
• Check constraints

create table if not exists country(
    country_id serial primary key,
    country_name text
);
--------------------------------------------------------------------
create table if not exists person(
    person_id int primary key,
    identifier text constraint uq_identifier unique,
    person_name text,
    country_id int references country(country_id),
    salary numeric check(salary >= 800)
);
------ Sample data for country -------------------------------------
insert into country (country_name)
values ('Bosnia and Herzegovina'), ('Russia'), ('Egypt');
------ Sample data for person --------------------------------------
insert into person(person_id, identifier, person_name, country_id, salary)
values (1, '129hffbv0vqpas', 'John', 1,  800),
       (2, '09vnksadvj89', 'Sebastian',  2, 1500),
       (3, '12q84ywvuhjffg', 'Joe', 3,  2000);

For the purpose of our tests, we will be using these two beautiful tables. SQLfiddle

— — PRIMARY KEY — —

Primary key constraints are simple enough to understand, they’re the main identifiers of many tables and are usually in sequence (ex. 1, 2, 3, 4, etc.). In many cases some companies and organizations have need to use a non sequential primary key. In such cases, there’s usually a custom made sequence (like social security numbers). The obvious thought going through many people’s minds is: “Why not always include not null at the end of a primary key syntax?” and I agree! But, let’s assume someone simply forgot to put it in. It pays off to know whether or not it’s all going to hell tomorrow if someone decides to insert NULL for all users or, if it’s alright and the company will survive.

Alright… Let’s start the tortu — *cough*, sorry, I meant to say “our testing”. Say hello to John, he will be joining us (again) to show us a lot of interesting things!

update person set 
   person_id = null
where person_name = 'John';

PostgreSQL answers back with:

ERROR:null value in column "person_id" violates not-null constraint

Great news! The primary key constraint enforces by default the NOT NULL constraint, even if you don’t specify “NOT NULL” at the end. Maybe it’s all good, right? PostgreSQL is stopping NULL inserts so far, so what about the rest?

— — UNIQUE — —

So, NULL didn’t go through the PK constraint but,what about the unique constraint?

update person set
    identifier = null
where person_name = 'John';

PostgreSQL answers back with:

Updated rows: 1

Oops… There goes John’s social security number, there’s a unique constraint over the identifier column but, PostgreSQL says it’s all fine!

And just in case you’re wondering, yes, you can indeed assign NULL to all other users in the database, not just John:

update person set
    identifier = null
where person_name = 'Joe';

Also works. OK, maybe it’s not as good as we thought it would be. But, it can’t all be bad, right?! Maybe foreign keys behave the same as primary keys?

— — FOREIGN KEY — —

Let’s not beat around the bush, let’s just run the code and see if it all goes to hell or not.

update person set 
    country_id = null
where person_name = 'John';

Well…

Updated rows: 1

Shit… There goes John’s country. There’s a FK constraint but it still allows you to insert NULL into the table. NULL obviously doesn’t exist in the country table but, it still passed through.

OK, maybe it’s horrible and only the PK constraint is secure from all this chaos. But, we still have one last piece of code to run to be sure.

— — CHECK CONSTRAINT — —

You know how sometimes you just give up to the cruel reality of this world and just expect things to go bad right away?

update person set
    salary = null
where person_name = 'John';

Well, we both know what’s going to happen at this point.

Updated rows: 1

Well, there goes John’s salary, social security number and even his country… There’s a check that says: “only insert a value if it’s bigger than or equal to 800”, but PostgrSQL says it’s all fine and dandy. Even I am starting to feel bad for the guy, let’s give him a raise.

update person set 
    salary = 10000,
    country_id = 1,
    identifier = 'bestIdentifier'
where person_name = 'John';

I just realized that I lied about giving John a rest from this whole thing… And we haven’t even started with the real thing (no rest for the wicked as they say). It’s time for round 2!

create table if not exists john(
    left_leg text,
    left_arm text,
    head text not null,
    right_arm text,
    right_leg text
);
--------------------------------------------------------------------
create table if not exists the_void(
    left_leg text default null,
    left_arm text default null,
    head text default null,
    right_arm text default null,
    right_leg text default null
);
insert into john values(null, null, 'Head', null, null);
insert into the_void values(null, null, null, null, null);

SQLfiddle

When I said John was going to the chopping block, I meant it literally (as you can tell from the code). It’s one of those moments when you become aware you might have been a sadist all this time and you’ve just realized it.

John’s probably going to kill me one day for all of this

Let us start with a simple query to remind us that = does not work on NULL.

select *
from john
where left_arm = null

And as expected, this returns nothing. But, what about join, intersect and except?

Let us start with the simplest one, join:

select *
from john j
    join the_void tv on j.left_arm = tv.left_arm;

Great news, join by default ignores NULL (but you probably already knew that). It now comes down to intersect and except.

select left_arm
from john
intersect
select left_arm
from the_void

If by some chance you end up with NULL on both sides, intersect considers NULLs as equals! Of course, if there is a non-NULL value on one side it won’t work (thank God).

select *
from john
intersect
select *
from the_void

This query returns nothing which is expected and good at the same time. Because intersect compares rows and looks for identical rows from the first query that exist in the second query. John happens to still have a head therefor, it didn’t select anything as the void has nothing.

But, based on this you can guess that except does the opposite:

select *
from john
except
select *
from the_void

It does end up selecting John’s entire body which includes all NULLs as well. Now, this could easily be what you actually want but, it’s always better to be safe than sorry and be sure that you are ready to receive these NULLs.

It’s finally over. Source, Flynn: Son of Crimson

We have come to the end of our adventures with NULL, and John can finally take a rest (until next time that is). But, before I end this article, you might be wandering “You didn’t give us a solution to all these horrible NULLs!”. Believe it or not, the solution is “simple”, you can use coalesce to avoid NULLs but, it’s up to you to recognize where they could cause a problem.

With that, I leave you in good health and bid John a farewell. Until next time!