All-too-often, database developers and administrators use Nulls, Zeroes, and Empty Strings interchangeably within their database tables. That's unfortunate, because Null, Zero, and an Empty String each represent something different in relational databases (RDBMS). As such, using these values incorrectly, or choosing the wrong one, can have enormous ramifications on the operation of your database and applications that rely on it. In today's blog, we'll explore how to best utilize the Null, Zero, and Empty String in database design and general usage.
What is Null?
The value Null has a long history in both relational databases and programming languages. It was devised as a special value to represent the intentional absence of any value. As such, it can be assigned to any nullable column. To designate a column as nullable, simply include the NULL keyword, or just leave it out, as columns are nullable by default:
CREATE TABLE table_name ( column1 datatype [ NULL | NOT NULL ], column2 datatype [ NULL | NOT NULL ], ... );
Navicat greatly simplifies the creation of tables via its Table Designer. In Navicat, to specify that a column may not contain NULL values, check the Not null checkbox:
In the Table Grid view, NULL values are represented as (Null):
In terms of semantics, a missing value informs us that we do not know it. Put more simply, a Null could mean "???". As we'll see shortly, this is not the case for either Zeroes or Blank Strings.
The Zero Value
A value of Zero (0) is a real number whose meaning is shared by other terms throughout the world, including nought (UK), naught (US), nil, zilch, zip, nada, scratch, and goose egg. Its value can be thought of as "Nothing". Consider a credit limit column. In that context, a value of 0.00 would indicate that the customer does not have credit. If the column is nullable, then a NULL value would mean that we don't know what the customer's credit limit is. In the first instance, we know what the customer's credit limit is, and that limit is zero.
In Navicat, we can set the default value of a column via the Default drop-down:
Note that NULL is the first option.
The Empty String Demystified
Much like Zero, an Empty String ("") differs from a NULL value in that the former specifically implies that the value was set to be empty, whereas NULL means that the value was not supplied or is unknown. As an example, let's consider a column that stores a cell phone number. An empty value would imply that the person does not have a cell phone, whereas a NULL would signify that he or she did not provide a number. These are two very different interpretations!
Conclusion
It is crucial for the database developer and administrator to understand the semantics of Nulls, Zeroes, and Empty Strings because using them incorrectly, or choosing the wrong value, can have enormous ramifications on the operation of the database and applications that interact with it.
To that end, Navicat Database Development and Administration clients facilitate working with Nulls, Zeroes, and Empty Strings by providing a Default drop-down and by clearly denoting Nulls in database tables.
