Is there any merit in creating a policy that requires developers to specify default values for all null columns?
A:
yes, consistency
keeping everyone on the same page is important, and this will ensure your devs don't get unexpected values in production.
I think it's a great idea.
Robert Greiner
2009-06-08 15:22:35
+3
A:
if you require a DEFAULT value for all NULL columns, you can make them all NOT NULL!
KM
2009-06-08 15:33:19
Isn't it possible for some business cases that although a column has a default value, a null is still a legitimate value?
Chris Farmer
2010-04-09 04:01:17
it is possible to put a NULL into a nullable column that has a default. I would imagine that under most cicrumstances that this would be considered a bug (forgot to make column not null). Developer would say "gee, I thought I had a default on the column, why do I have so many nulls in there?" defaults are only only used if you do not specify the column in the insert, or if you specify it and pass in the DEFAULT keyword. However, business is a funny thing, so I guess that any crazy requirement is possible. User says I want the field to default to zero, but also be able to be unknown sometimes.
KM
2010-04-09 11:13:57
+1
A:
Nulls are often a source of confusion, due to the three value logic they involve. For example, the query...
select * from employee where job != 'MANAGER'
... might be expected to return all employees who are not managers. However, of course, if JOB can be null then this query will not return employees whose JOB is null.
It could be that this is the rationale behind the default values policy.
Tony Andrews
2009-06-08 20:11:04
+1
A:
Assigning default values only shifts the problem because essentially those default values carry the same "meaning" as the nulls do, so you are then facing a requirement to include special-casing in your code to deal with the "special meaning" of those "special values".
Yeah, but you reduce the question from "what could null mean"? in a bunch of problem spaces, to what could "default value of 0 mean". Taking the employee example from above, while an employee record with job == null could mean both that the employee hasn't been assigned a job yet, or the employee has been fired, a record with employee being defaulted to "unassigned" would be more meaningful.
Irwin
2009-06-10 12:53:53
A:
@TonyAndrews :
... might be expected to return all employees who are not managers. However, of course, if JOB can be null then this query will not return employees whose JOB is null. It could be that this is the rationale behind the default values policy....
NULL was invented to handle missing value, so for your query its completely rational to not return rows with null value in job field! You are asking DBMS to return rows which job's are not 'Manager'; it does means not return me those whose jobs are unknown. if you want missing jobs as well, thats different question.
just imagine may be some managers are there which their job fields are NULL(missing, not entered yet) so if database return them, query's result would be absolutely wrong!
coming back to main question, i think if a field is NULL allowed it should have NULL in the case of missing value, otherwise if the field never has missing value(or always needs default value) just make it NOT NULL with default value. and this is based on business logics in a specific condition.
Ehsan
2010-04-09 03:51:20