How to make NOT NULL the default when creating new columns

  • Thread starter Thread starter Tomas Eklund
  • Start date Start date
T

Tomas Eklund

Using: MS Access 2003

The default in Access is to allow all columns to accept NULL values. I'm annoyed
that for every column I create I have to change the Required property to No
manually. Is there a way to change the default value so that Access always
assumes that I do NOT want to accept NULL values in my database tables?
 
Sorry. I made a typo. It should have been:

"I'm annoyed that for every column I create, I have to change the Required
property to *Yes* manually."
 
I don't know of a feature that allows this. However, this idea has come up
more then once, and perhaps future versions will have this feature.

On the other hand, since any query join will OFTEN result in nulls, then you
code and designs have to work with nulls anyway, even if you don't allow
them. Since a VERY large portion of my joins are left joins..then nulls are
retuned even when none of your fields are null.

So, as a design rule, I do NOT allow zero length strings, and assume all
empty stings fields are in fact null.

In your case, many of your queries will now have to deal with

where CustomerName = ""
or
where CustomerName is null

If you design as such that all empty string fields are null..then you only
have to deal with:

where CustomerName is Null

Anyway, the above really is no hard and fast rule..and whatever setup you
choose..you need to stick to (and, thus I very much understand your
question..and need for a constant "default". For sure, the one rule that
stands is go with a particular choice..and then have all your code and setup
follow your choice.
 
Thomas, I agree with Albert completely. As he points out, you still have to
learn to handle nulls, even if there are none in your tables.

You may find this article helpful:
Nulls: Do I need them?
at:
http://members.iinet.net.au/~allenbrowne/casu-11.html
There is also a companion article describing how to work with the 6 most
common errors people make with Nulls.

If you do understand nulls, are comfortable with programming, and want to
disallow nulls anyway, you cannot set an option within Access, but you can
programmaticlly modify the Required property of each Field in each TableDef
in your Database, like this:

Function SetRequired()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb()
For Each tdf In db.TableDefs
If (tdf.Attributes And (dbSystemObject Or dbHiddenObject)) = 0 Then
For Each fld In tdf.Fields
If Not fld.Required Then
fld.Required = True
End If
Next
End If
Stop
Next
End Function

Note: This code applies to future changes, and does not modify existing
nulls.
 
Thanks, both of you, for your input and for the link. It was interesting
reading.

More often than not do I NOT need nulls in my columns. I rarely need to
distinguish "middle name unknown at this time" from "this person has no middle
name" so an empty string is sufficient in both cases. And empty strings are much
easier to handle than nulls in code (as the linked page so well points out). I'm
glad that at least "Allow zero length strings" defaults to Yes.

One situation I can think of right now where I might want to use nulls is when I
have a pointer (a foreign key) that sometimes should be pointing nowhere. I
believe it's better to allow nulls in that case, than to have a "reserved key
value" like -1 or 0.

What you said about "you still have to learn to handle nulls" makes sense. And
I'm starting to see a point in actually using them. What set me off in the
"avoid nulls direction" was this article:

http://www.aspfaq.com/2073

Thanks
 
I could not disagree more strongly.

Maps confuse people too. It does not mean they are useless. You learn how to
handle them. Computers display strange and inconsistent bevavior, but that
doesn't mean you should avoid them.

Nulls are an important part of database theory and practice. Zero Length
strings (ZLS) are NOT an appropriate data type for numbers, nor for dates,
nor some other data types. That means you now have a disparity between the
way you handle Text fields and other data types. For example, if you COUNT
your numbers, the nulls are not included, but if you COUNT your Text fields
with their ZLSs, they are included.

Whoever wrote that page did not understand Nulls or how to use them. Do not
give up on learning how to deal with this a very basic, fundamental,
absolutely essential concept.
 
Tomas Eklund said:
What you said about "you still have to learn to handle nulls" makes
sense. And I'm starting to see a point in actually using them. What
set me off in the "avoid nulls direction" was this article:

http://www.aspfaq.com/2073
Click to expand...

I just read over that article, and I have to say that I disagree with it
quite strongly. While the author is no doubt entitled to his or her
opinion, the majority of points seem to revolve around the essential
viewpoint that Null values don't behave the way the uninitiated expect
them to behave, and therefore should be avoided. (Note: that's my
gloss on the article, not a quote.) However, the behavior of Null is
not random, nor "strange and inconsistent" (as McGoveran and Date are
quoted as saying) -- it is in fact very well defined. It makes pefect
sense to me, and I find it an essential concept in data modeling.
Frankly, I find the the widespread use of dummy values, as the article's
author recommends, abhorrent. I should create artificial values to
represent the fact that there are no values? That's what Null is for!

The only points in that article that I haven't enough information to
argue with are those having to do with data warehousing and OLAP issues.
I don't know enough about the WITH ROLLUP and WITH CUBE clauses to know
whether the problems that the author refers to are true problems, or
just matters that you have to understand before you go writing queries.
I'll reserve judgement on that.

Should you use Null when it isn't appropriate? No. Should you use Null
when it is appropriate? Yes. You'd have to make a much better argument
than that article presents to convince me otherwise.
 
Back
Top