Tony:
Numerical columns have a DefaultValue property of zero for good reason. A
NULL is not a value, but the absence of a value, an 'unknown'. It must not
be confused with zero. A property off NULL is that it propagates, i.e. in an
arithmetical expression involving a NULL the result will always be NULL.
Consequently if a numerical column (field) is NULL then unexpected results
might be obtained. Say you had a column NetPrice and a column TaxRate for a
sales tax or the European Value Added tax (VAT) expresses as a fractional
value. You would use an expression such as the following to compute the
gross price in a form, report of query:
NetPrice * (1 + TaxRate)
If a product or service did not attract a sales tax or was zero rated for
VAT and the column was left NULL the NULL in the above expression would
propagate to give a gross price of NULL. This is not an incorrect result as
far as the data is concerned; its exactly the right answer. The fault is in
the data by virtue of the use of the NULL. A DefaultValue of zero and a
Required property of True (equivalent to the NOT NULL constraint in the Data
Definition Language (DDL) of SQL) would mean that the above expression would
always give the expected answer as the result would be the same value as the
NetPrice where the tax rate is zero.
NULL also gives rise to problems over the meaning of a column where it is
left NULL as its semantically ambiguous. What does a NULL credit rating for
a customer mean. Does it mean 'no credit', or does it mean 'unlimited
credit' or what? There is simply no way of knowing from the data itself (or
more precisely from the absence of data, because NULL is not data at all).
Even where you might think there is no alternative to a NULL this is not
necessarily the case. Take towns and cities for instance for which you might
have a City column in a table Cities. In the UK a small town like Stafford,
where I live, is in a County, but large cities like Liverpool, where I was
born, are not in any county these days. They are independent unitary
authorities. You might think that you'd leave the foreign key CountyID
column in Cities for Liverpool NULL, which many people would. However, this
might create a problem because if the database is an international one you
would probably have a Counties table and a foreign key CountryID column in
the Counties table (you'd probably call it something more generic to cater
for different international regional units as County here is very different
from a County in the USA for example) referencing the key of a Counties
table. If County is NULL in the Cities table, however, then there is a
missing link in the chain and we would not know what country Liverpool is in.
The answer is to have a row N/A in the Counties (or whatever) table and a
CountryID value in that row referencing the UK row in the Countries table.
You'd have other N/A rows in Counties referencing other countries where
similar situations apply, or even where they have no regional units between
City and Country at all. There are alternative ways of doing this involving
the use of multiple column keys but these also rule out the use of NULL, as
part of a primary key cannot be NULL.
One place where its difficult to avoid using a NULL, however, is with a
date/time data type column. This will accept a NULL or a true date/time
value only, so the use of a value like 'N/A', 'Unknown' etc is ruled out.
I've strayed a long way beyond your original question, but I'm merely
trying to illustrate how allowing NULL in a column is not always as innocuous
as it might seem.
Ken Sheridan
Stafford, England
