In Defense of Nulls (5)

In Defense of Nulls

Default Values

C. J. Date has proposed the use of default values as a replacement for nulls in relational database. Recently, David McGoveran extended this proposal with the suggestion that a special mark be used to represent default values. (1) This confirms the view that default values are but a veneer placed over nulls. In other words, this is like a system where all columns that allow nulls also have a default value. What we have here is a system with ersatz nulls without the benefits of 3VL.

The problem in this scheme is that users of the database will attempt to use default values in the same manner as nulls. Properly, I might add, because there is a need to be filled here. Given the classic Supplier/Part table (sp) containing supplier number, part number and quantity (s#, p#, qty), a user might inquire as to what suppliers supply a certain part (P1). The user wishes to ignore default values for p#. A simplified part of the query we are interested in could be :

WHERE (p# IS NOT DEFAULT AND p# = 'P1') If the user then wished to know suppliers which definitely did not supply that part, the user might try a simple translation :

WHERE NOT (p# IS NOT DEFAULT AND p# = 'P1') But that would be incorrect. A correct formulation would be :

WHERE (p# IS NOT DEFAULT AND NOT p# = 'P1') This is a simple query. For more complex queries, the possibilities for incorrect formulation grow. Contrast the above with both query fragments expressed using 3VL :

WHERE (p# = 'P1') and WHERE NOT (p# = 'P1') In the next section, Multiple Types of Nulls and Multi-Valued Logic are discussed.

Return to Contents Page: In Defense of Nulls Return to Issues Page