|
|
Posted by Erland Sommarskog on 10/31/05 01:35
serge (sergea@nospam.ehmail.com) writes:
> At work we are setting all tables' columns to have constraint defaults
> of 0 or ' ' (space character) in order not to have any column with the
> NULL value. Therefore we have dozens of files containing statements like:
I missed this part. I completely agree with Hugo, and I even go so far
as saying that this is an extremely poor idea.
OK, so for character data, it may be difficult to find a real-world example
when NULL, empty string and a single space would really mean different
things.
But for other datatypes it's necessarily not so. In some contexts a 0
for a numeric value can serve as a NULL, because the actual domain for
the value is >= 1. Or if the domain is >= 0, you can use -1. Because that
is really what you need: an out-of-band value to represent the unknown.
For dates, there are probably a whole lot of dates you can use. But
there starts the problem. Which value did you use to represent N/A for
this column?
It gets even worse with foreign keys. In an OrderDetails table, there
is a CampaignID column for the case the customer order the product as the
result of a campaign. But what if there is no campaign. Do you add a
"NoCampaign" row to your Campaigns table? A NULL is so much easier to
handle.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|