Is there a compelling reason why columns in SQL are nullable by default?

Question

As a CS student, I've learned a decent number of programming languages over the years, most of which have had some concept of a "nullable" or "optional" type. Note that I'm not talking about null pointers or references, or weakly-typed languages like JavaScript where anything can be null. Examples of what I'm talking about include boost::optional (C++), java.util.Optional (Java 8.0), prelude.Maybe (Haskell), and all of the '?' types (e.g. int?, float?, C# and Kotlin). These are constructs that add nullability to a previously non-nullable type within a strict, static type system.

SQL has a similar concept: a type such as INTEGER can be made nullable or non-nullable--but there's a twist. In SQL, INTEGER is nullable by default, and must be explicitly written as INTEGER NOT NULL in order to be non-nullable.

It strikes me as extremely counter-intuitive and potentially dangerous for allowing NULL's to be the default behavior. Obviously SQL's been around for so long at this point that (most) SQL developers have developed a healthy awareness of the pitfalls of NULL. But I can't help but imagine that in the early days NULL's often crept up in unexpected and problematic places.

SQL does predate all the examples I've provided, so it's possible that this is simply a matter for historical evolution. Still, I have to ask, is there any good reason for the language to be designed this way, with types being nullable by default?

If so, is it just a historical reason, or does the logic hold up for database design today?

Edit: I am not asking why NULL is a part of SQL or why nullable columns are useful. I am just asking why column are nullable by default. For example, why do we write:

column1 FLOAT,
column2 FLOAT NOT NULL

Rather than:

column1 FLOAT NULLABLE,
column2 FLOAT

Answer 1

At Uni I was taught that the opposite is true. It's much more dangerous to make something not null without reason. With a nullable field the worst thing that can happen is you trip over the application accessing the data. Oh dear, go back and fix the app...

With a not-null field you make it impossible to add record because some arbitrary field isn't available. Now you need to change the data model and potentially fix the result in a LOT of different places...

It's good to think of null as "unknown". If there's any plausible reason why you might want to enter a record without knowing something then it should be nullable.

One of my university lecturers described it like this:

Apocryphally I've heard of a sales system in the USA which required customer's social security number to make a sale. All the till operators did when a foreigner came to the till was enter 000-00-0000. But then others would enter 123-45-6789. This makes it impossible to identify junk. It's much better to allow a field to be blank than to force it to contain junk.

Or another story. I have genuinely been refused car insurance because I don't have two phone numbers. They absolutely would not give me insurance unless I gave them two. The sales guy suggested I just give a false one. In the end I refused to lie to an insurer and just went with another company.

In practice reserve not null for fields which are required to make sense of the record. For example:

A table of places with fields (ID, Place Name, Country, Longitude, Latitude) ... "longitude" "latitude" should be nullable so that you can store the existence of a place before you know where it is.

But if you have a table who's sole purpose is to store geographical coodinates with fields (Item_id, longitude, latitude) the entire record is meaningless if longitude and latitude are null. Therefore in this instance they should be not-null

In my professional experience since uni, there are far more fields which can optional than need to be mandatory.

Answer 2

It strikes me as extremely counter-intuitive...

Intuitive is in the eye of the beholder and your opinion on that is shaped by the things to which you've been exposed. I hail from a time when that kind of safety wasn't standard and the tools didn't point out when you goofed up. I've been using the chain saw without a blade guard long enough that my first instinct is to avoid intuition entirely, go back to the DDL and find out exactly what assumptions the schema will let me make about its data.

...and potentially dangerous for allowing NULL's to be the default behavior.

I think you're overstating the relative dangers. NOT NULL has its own set of pitfalls that can lead to equally-insidious bugs. (Enumerating them would be fodder for a different question.)

The designer of a table always has the option of constraining a column NULL or NOT NULL and will do one or the other to get around the default, whatever it is. Not constraining a column correctly is a developer's failure to follow the business rules. Not doing the right thing elsewhere based on the column's definition is a developer's failure to understand the data he's being handed. There's no technical fix for either.

Still, I have to ask, is there any good reason for the language to be designed this way, with types being nullable by default?

No, there isn't. Because both have hazards, there's also no good reason for the language to be designed the other way. It boils down to picking your poison.

Answer 3

Nullable columns are necessary in SQL due to outer joins (also known as left joins or right joins). When row on one side of the join does not have match on the other side, the fields for the other side must have NULL's. Since the output of a join can have nullable columns, base tables should also support them due to the principle of relational closure (which basically state the result of a query or view should be indistinguishable from a base table).

Given this, SQL must support nullable columns. On the other hand, non-nullable columns are a secondary feature - SQL could still work without them.

Answer 4

Let's turn it around and say you're right. Let's say your integer is not null by default.

Which means it has to have a a value by default. Even when it's not known.

So when you update your persons table and you either have two choices: It's impossible to update the table because you didn't input weight. Or when you didn't supply the weight argument it put in the standard "-1 kilos" when unknown.

Both situation are undesirable. You want to be able to add customers, even if you don't know their weight. But also, you don't want to have "proxy" values. Values which are placeholders but can have real meaning, for instance: can be used in math-functions like "average" but aren't real values.

I mean when calculating an average weight, -1 is a valid value in your math average function, but not as a persons weight. You use null and now your average function knows to ignore that value.

Also, I wouldn't really compare SQL to programming languages when discussing nulls, they're inherently different, null in SQL is very much part of relational database design theory.

Answer 5

No. There is no compelling reason why SQL defaults to nullable. In fact, many prominent researchers in relational database theory have disagreed with this design decision, perhaps most notably Chris Date, a frequent collaborator with the original designer of the relational database, Edgar Codd. Date (along with coauthor Hugh Darwen) published a well-known book on relational theory ("The Third Manifesto") that describes principles for alternative designs for a family of relational languages they call "D", along with an example such language called "Tutorial D".

D languages are explicitly proscribed from supporting NULL values ("D shall include no concept of a “relation” in which some “tuple” includes some “attribute” that does not have a value."). Instead, optional values are supported by having alternative data types that include placemarker "not-present" or similar values. D languages provide a rich model for user-defined types that would allow any native type to be extended with such extra values.

There are compelling theoretical reasons why this is a good idea, and Date & Darwen have written a lot about this, as well as the other decisions they made in their design. I highly recommend reading their work on this topic.

Answer 6

I'm not disagreeing with your premise about what the default should be, but it's a good practice to assume nothing as a developer. Checking the specs on a database table shouldn't be too difficult.

More from a DBA perspective where you'd be asked to bulk load data especially when merging from other systems, you better know the setting for each field whether you have any data to put in it or not.

Businesses and applications are run by people. If they're not a programmer, the definition of "never" and "always" are not exactly the same and will change over time. The current null setting on a given field shouldn't be fuzzy.

Answer 7

Databases are different beasts from normal programming languages.

Because the schema of a table is set all the data needs to be present when saving the information to a row. However many of this data may not be required to create a valid representation of a model object once loaded in your code. Requiring that all data must be non-null and populated will mean that these non-required fields will have to contain a value and yet they do not have one yet, they are "unknown".

Imagine having to fill ALL fields on web forms ALL the time since they cannot be null in the database they must receive a value... a recipe for insanity that is !

You could set some reserved values to represent the absence of data, an empty string, a specific number, a specific date etc depending on the data type but what value to choose ? Then you need to make sure that everyone agree that these arbitrary values actually mean "unknown" and not "January 1st 1970" for example. Null aversion can take many forms and take you on long convoluted detours just because someone said nulls were bad. How complex are you ready to get just to avoid dealing with nulls ?

Having a single universal value for everything that is unknown I find much preferable than using some set of arbitrary constant values. I'm not saying constant values are bad and null is better, if your model is well served by a constant to represent this information then by all means use that but there are many situations where a null is just what fits best. For all null haters, this is a situation if null was denied it would have to be invented !

Seeing how pervasive the concept of "unknown" is in a database then yeah, I'd say that making the values nullable a default makes a lot of sense.

Going deeper and looking at other answers here I would not be surprised to learn that nulls are not just a "language feature" but an integral part of the underlying theory on which SQL is based. One can remove C (the speed of light) from the relativity, but the concept of absolute maximum speed remains and must still be expressed so it will be back in some shape or form.

Answer 8

Short answer: backward compatibility.

Long answer:

In a fully normalized database, NULL is not allowed in any column. For example, suppose there is a table called MailingAddress which has a column PostOfficeBox, which is an integer. Since not everyone has a post office box, there are two ways to implement this.

First, NULL could be allowed in the column.

Second, PostOfficeBox is removed from MailingAddress and a new table, PostOfficeBox is created with a column Number and its PK being the FK to MailingAddress. But now two queries are needed to get mailing addresses: one for those without post office boxes and one for those with.

SQL allows NULLs in columns for practical purposes.