> This sound interesting, but I understand what the problem
> is. Isn't a declaration "not null" on fields that can't
> be null sufficient? Please explain a little more.

"NOT NULL" is sufficient to enforce the requirement, but
not enough to make things work well in common situations.
There are several parts to this...


Performance and efficiency:

Many interfaces prepare a full insert statement regardless
of whether values are entered into a particular field by the
user. Thus NULL will often get submitted by the application
and prevent normal DEFAULT values from being applied by the
server (and so NOT NULL on the fields result in exceptions).

This can be avoided by preparing a new insert statement for
each insert, so the insert statement can be created to only
include fields with values supplied. As you can imagine this
is far from efficient if many inserts are being performed.

Some interfaces allow default values to be defined, or even
to read default values back from the server - and so such
defaults can be applied at the client. But databases should
not normally be written to depend on a particular client.

Having a default that can apply to null columns, whether the
NULL was specified on the insert or the result of the column
not being specified, allows inserts to be more efficient.


Convenience and clearer code:

If the enforced assignment of default values was to occur
before any insert or update triggers are fired it becomes
possible to write trigger code that can assume valid input
values - allowing the code to be much clearer and more
succinct.

I included update triggers, even though it may be redundant
much of the time, because it can be convenient for an
application to assign NULL as a way of resetting a value to
its default without having to read that default from the
database. Its also a way of ensuring that even update
triggers do not need to worry about explicitly assigned NULL.

The equivalence operator being discussed would go some way to
resolving the code clarity issue, but not all the way. For
example:
if (fielda == 0 and fieldb == '')
-- blah blah

NULL is not the same as 0 or '' even under equivalence test.
If the default had already been applied then (if the default
was 0 or '' as is quite common for many columns) then this
statement would be adequate without extra null testing.

Side Note: Such a pre-trigger process would not prevent
trigger code from explicitly setting a column to NULL (which
would then be caught by NOT NULL checking), but anyone can
write code to break things if they try so I dont see this as
a particular problem.


Reflecting reality:

In a great many applications the distinction between NULL
versus "empty" (0 number or blank string) is not relevant.
Indeed in most of my work the users really do not want to
know about "missing" values, it is not of any interest. If
they dont supply the value they generally expect it to be
empty (not missing/null/unknown).

I am not arguing that this applies to all applications. I am
sure that some find it convenient to make this distinction.
But in most of what I do the distinction is just annoying, so
I write many many triggers to ensure it never applies.


Clear design declaration:

If I can create my tables/columns such that enforced defaults
are there in the declaration, I have not only reduced the work
of writing triggers, I have also declared the intentions of my
design in clear and easy to read form (rather than browsing
huge amounts of trigger code). And this has got to be a good
thing.


I hope this explains what you wanted to know.

--
Geoff Worboys
Telesis Computing