| Lesson 9 || Relational Database Null Values |
|Objective || Explain the purpose of null values in a Relational Database |
Null Values in a Relational Database
Every column in a table should contain a value, though there may be times when the value is unknown. For example, consider the following table, which stores data relating to Stories on CD suppliers.
Columns of the SUPPLIER table 1) SupplierId, 2) SupplierName ,3) SupplierAddress, 4) SupplierPhone, 5) SupplierFax
To communicate with suppliers you will need their name, address, phone number, and fax. If you do not know one or more of those pieces of data, you will not know what to enter into its corresponding column.
When the value to enter into a field is unknown, it is called a null value.
A null is different from a blank or a zero. A blank is the value used when a column contains no value.
For instance, if the supplier you met with does not have a fax number, you would enter a blank value into that column. A zero, by contrast, is an INT or DECIMAL value.
If a Stories on CD supplier gave the company a thousand free CDs to send to the next one thousand customers who placed an order, the RetailPrice column for that CD would contain a zero.
Blank and null used interchangeably
In many cases it appears that a blank and a null can be used interchangeably. However, the distinction is important to the RDBMS. If a value is truly unknown, enter a null into the column. You can represent a blank by typing a single space in the field. How you enter a null into a field will vary from RDBMS to RDBMS, but one common method is to skip the field entirely by pressing the Tab key.
The columns that make up database tables fall into one of two general categories: key columns and descriptor columns.
The next lesson discusses the difference between them. Before you move on to the next lesson, click the link below to reinforce your understanding of the distinctions between nulls, zeros, and blanks.
Null Zero Blank Values
Rule 3: Systematic Treatment of NULL Values
What Are Codd's Rules of an RDBMS?
Rule 3 states that NULL values (distinct from an empty character string, a string of blank characters, or a numeric zero value) are supported in the RDBMS as a systematic representation of missing information, independent of the data type of the column containing the NULL value. This means that the database engine has to allow NULL values for any data type, as distinct and different from zeros, spaces, and N/A. This emphasizes the importance of the database supporting defined nullability (the ability to not have any value at all) and optionality (the ability for optional relationships to other data sets).